518 words

Performance issues on aggregate query

#1
2016-11-09 07:49

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 ?

 

 

hgzwicker
hgzwicker's picture
Joined on 2014-04-09
User Post #21
#2
2016-11-09 22:41

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.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support
support's picture
Joined on 2010-05-03
User Post #2,633

Post Reply

Please read carefully the posting instructions - before posting to the ObjectDB website.

  • You may have to disable pop up blocking in order to use the toolbar (e.g. in Chrome).
  • Use ctrl + right click to open the browser context menu in the editing area (e.g. for using a browser spell checker).
  • To insert formatted lines (e.g. Java code, stack trace) - select a style in the toolbar and then insert the text in the new created block.
  • Avoid overflow of published source code examples by breaking long lines.
  • You may mark in paragraph code words (e.g. class names) with the code style (can be applied by ctrl + D).
  • Long stack traces (> 50 lines) and complex source examples (> 100 lines) should be posted as attachments.
Attachments:
Maximum file size: 32 MB
Cancel