Performance issues on aggregate query

we are using this aggregate query to collect some data:

select p3.doubleValue,c1.classIdentifier,c1.objectName,sum(p4.doubleValue),sum(p4.doubleValue*p5.doubleValue),0,p7.doubleValue,p3.name,count(o) from ObjectNode o INNER JOIN o.properties p1 INNER JOIN o.properties p2  INNER JOIN o.properties p3 INNER JOIN o.properties p4 INNER JOIN o.properties p5 INNER JOIN o.properties p6 INNER JOIN o.properties p7 join o.linkedObjects c1 where o.classIdentifier = '(OP)' and (o.linkedObjects.classIdentifier = '(TC)' and o.linkedObjects.objectNameUpper = 'MILLING' and o.linkedObjects.type = 1 and o.linkedObjects.state = 0) and (p1.name = 'feedbackState' and p1.doubleValue != 13 and p1.state = 0) and (p2.name = 'scheduledState' and p2.doubleValue = 2 and p2.state = 0) and ((p3.name = 'scheduledEndSlot' and (p3.doubleValue > 2443 and p3.doubleValue<2452)) or (p3.name = 'scheduledSlot' and (p3.doubleValue > 2443 and p3.doubleValue<2452))) and (p6.name = 'roughPlanning' and p6.doubleValue = 0 and p6.state = 0) and (p7.name = 'slots') and (p4.name = 'multiplier') and (p5.name = 'intendedBases') group by p3.doubleValue,c1.classIdentifier,c1.objectName,p5.doubleValue,p7.doubleValue,p3.name

 

these are the classes that are used:

public class ObjectNode implements Serializable {
    private static final long serialVersionUID = 1L; 

    @Id
    public String uUid;

    public Integer associatedFileCurrentVersion;
    public Date associatedFileLastModification;

@Index public String classIdentifier;
public Date created;
public Map<String, String> currentJournalData = new HashMap<String,String>();
public String customIcon;

@Index public String fileURL;
@Index public String foreignUuid;

@Index public String identCode;
public String internalReference;

// EUR-0004
public Boolean inSlots = true;

@Index
public Date modified;

@Index public String nodePath;

@Index
public String objectName;
@Index public String objectNameUpper;

@OneToOne
@Index
public ObjectType objectType;

@Index
public Integer state;
public Map<String, String> systemDictionary = new HashMap<String,String>();

@Index
public Integer type;
public Double usedCustomObjectTypeVersion;
public Double usedStandardObjectTypeVersion;
public Double usedSolutionObjectTypeVersion;

@OneToMany(fetch=FetchType.LAZY)
@Index
public List<Action> actions = new ArrayList<Action>();

@OneToMany(fetch=FetchType.LAZY)
@Index
public List<ObjectNode> childNodes = new ArrayList<ObjectNode>();
 
@OneToMany(fetch=FetchType.LAZY)
public List<ObjectNode> container = new ArrayList<ObjectNode>();

@OneToOne(fetch=FetchType.LAZY)
//@Index
public ObjectNode currentLocation;

@OneToOne(fetch=FetchType.LAZY)
@Index
public ObjectNode currentOperation;

@OneToMany(fetch=FetchType.LAZY)
@Index
public List<ObjectNode> isCurrentLocation = new ArrayList<ObjectNode>();

// HS-685
@OneToMany(fetch=FetchType.LAZY)
@Index
public List<ObjectNode> isCurrentOperation = new ArrayList<ObjectNode>();
@OneToMany(fetch=FetchType.LAZY)
@Index
public List<ObjectNode> isNextOperations = new ArrayList<ObjectNode>();

@OneToOne(fetch=FetchType.LAZY)
public ObjectNode linkedType;

@OneToMany(fetch=FetchType.LAZY)
@Index
public List<ObjectNode> nextOperations = new ArrayList<ObjectNode>();

@OneToOne(fetch=FetchType.LAZY)
@Index
public ObjectNode parentNode;

@OneToMany(fetch=FetchType.LAZY)
public List<ObjectNode> physicalIdentities = new ArrayList<ObjectNode>();

@OneToOne(fetch=FetchType.LAZY)
@Index
public ObjectNode pointingTo;

// HS-2518
@OneToMany(fetch=FetchType.LAZY)
@Index
public List<ObjectNode> linkedObjects = new ArrayList<ObjectNode>();

@OneToMany(fetch=FetchType.LAZY)
public List<ObjectNode> precessors = new ArrayList<ObjectNode>();

@OneToMany(fetch=FetchType.LAZY,cascade=CascadeType.REMOVE)
@Index
public List<ObjectProperty> properties = new ArrayList<ObjectProperty>();

@OneToMany(fetch=FetchType.LAZY)
public List<ObjectNode> successors = new ArrayList<ObjectNode>();

@OneToMany(fetch=FetchType.LAZY,cascade=CascadeType.REMOVE)
public List<ObjectProperty> supportingData = new ArrayList<ObjectProperty>();

@OneToMany(fetch=FetchType.LAZY)
public List<ObjectNode> workflow = new ArrayList<ObjectNode>();

...

 

public class ObjectProperty implements Serializable {
    private static final long serialVersionUID = 1L;
   
    @Id @GeneratedValue
    public long id;

@Index
public Double doubleValue;
// HS-778
public Boolean journal;
public String jsonValue;

@Index
public String name;

public Integer source;
// HS-2078
@Index
public Integer state;
//@Index
public String stringValue;
@Index
public Integer type;
public String unit;
public String valid;

 

No the problem is the following:

when we query objects that have no objects linked into the relationship ObjectNode.childNodes the query takes around 1 second.

when we query objects that have typically 30-50 objects linked into the relationship ObjectNode.childNodes the query takes around 16 second.

For us that seems strange as the relationship ObjectNode.childNodes is not used at all in the query:

Are there other elements that we can optimize in the query ?

 

 

This is strange. It might help to check if the query plan is different with/without childNodes content (you can see query plans when running queries in the Explorer).

The SELECT clause in your post seems to be cut at the middle of a line. Sometimes when a query return entity objects, returning the results with all the eagerly reachable objects take more time than just executing the query (i.e. when every result object requires retrieval of a large tree of other objects), but this is relevant for queries that return entity objects.

As always, if you can provide a test case that demonstrates the problem it would help.