select from MyClass fetchplan *:-1; is SLOW (around 1000 docs/sec)

[pabloa]

Hello

I have a class UserTrace with a property trace of type LinkedList. Property trace is a list of other classes. 

I have several thousand documents stored in that class on a remote server.

When we send from Java a query like:

select from UserTrace fetchplan *:-1

We get the list back but performance is bad (around 1000 documents by second).

I added fetchplan *:-1 so the answer is a list of all documents with all the properties and subdocuments fetched all together. And still we get results at 1000 docs/second

Is it possible to improve performance? 

It seems to us that each element of userTrace.trace list is being recovered again from the database. Is that possible? Should "fetchplan *:-1" return each entry in trace list fully exploded?

This is the content of 1 document in UserTrace table.

Any help will be welcome.

Pablo

[Luca Garulli]

Hi Pablo,

Sorry but your issue lack a lot of information:

• OrientDB Version
• Are you measuring with studio? If yes, please consider the rendering time. In case use EXPLAIN <YOUR-QUERY> to have the real execution time
• How many UserTrace instances? And how many linked instances?
• What kind of HW/SW configuration are you using?

Best Regards,

Luca Garulli

Founder & CEO

OrientDB

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[pabloa]

Hello Luca,

• OrientDB Version
  2.1.19 
  
  

• Are you measuring with studio? If yes, please consider the rendering time. In case use EXPLAIN <YOUR-QUERY> to have the real execution time
  

• I am measuring using a unit test written in Scala using Java API. I verified I am using version 2.1.19 of the API.
  
  

• How many UserTrace instances? And how many linked instances?
  

• Database has 13008 UserTrace instances. UserTrace class contains only 1 linkedlist property. This linklist property has an average of 200 elements (290 elements max).
  
  

• What kind of HW/SW configuration are you using?
  

• We are using a QuadCore with 24gb of RAM, Java 1.8.0.77. JVM parameters are: -server -Xms512m -Xmx4g. Server is not swapping. 
  First lines of orientdb.err log:
  
  2016-06-08 13:54:02:477 INFO  OrientDB auto-config DISKCACHE=18,423MB (heap=3,641MB os=24,112MB disk=42,785MB) [orientechnologies]
  2016-06-08 13:54:02:543 INFO  Loading configuration from: /opt/orientdb/orientdb-community-2.1.19/config/orientdb-server-config.xml... [OServerConfigurationLoaderXml]
  2016-06-08 13:54:02:661 INFO  Disk cache size was changed from 250491 pages to 167076 pages [O2QCache]

Please let me know if you need more information

P

[Luca Garulli]

Ok,

With this query

select from UserTrace fetchplan *:-1

You're loading the entire database, because it would scan all 13,008 UserTrace records and the will load all the connected record. Is this what do you want?

Best Regards,

Luca Garulli

Founder & CEO

OrientDB

[pabloa]

Yes. This is what we want. We have queries like that and the idea is to recover all the classes (and all the linked sub classes).

The problem is that 

select from UserTrace fetchplan *:-1

and

select from UserTrace 

are solved in the same times. AKA Times do not improve with fetchplan *:-1

We suspect linked classes are being recovered again with or without "fetchplan *:-1". We thought that  "fetchplan *:-1" would send back to the client all the linked classes so there is not need to fetch them again.

In that context my question is why both queries have the same times to be solved. We believe the 1st query should be faster than the second.

Pablo

[Luca Garulli]

Fetchplan as optimization of transport is working only if you execute the query using the remote protocol, namely Java client and "remote:" in the URL.

With Studio (and HTTP protocol) the fetchplan is ignored.

Best Regards,

Luca Garulli

Founder & CEO

OrientDB

[pabloa]

Hello Luca

I execute "FETCHPLAN" query from java api using remote:.

If it is a transport optimization, it does not explain why the response takes the same time with or without FETCHPLAN keyword.

Behavior could be reproduced with a Class similar to the one I posted and less than 10000 rows.

Pablo