Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Modeling » EMF » [CDO] -Performance - 2n queries for fetching n objects.
[CDO] -Performance - 2n queries for fetching n objects. [message #1753523] Tue, 07 February 2017 12:47 Go to next message
Philippe EUGENE is currently offline Philippe EUGENEFriend
Messages: 37
Registered: January 2011
Member
Hi,
I made some tests on performance with CDO.
For these tests, i used h2 database and the Library EMF model.

I use tweaks from this page :
https://wiki.eclipse.org/CDO/Tweaking_Performance

I use this CDOCollectionLoadingPolicy and this RevisionPrefetchingPolicy
CDOCollectionLoadingPolicy policy = CDOUtil.createCollectionLoadingPolicy(0, 20);
session.options().setCollectionLoadingPolicy(policy);
transaction = session.openTransaction();
transaction.options().setRevisionPrefetchingPolicy(CDOUtil.createRevisionPrefetchingPolicy(20));


The server log show that CDO made 2 queries for fetching 1 objects.
I cut the log. I just left the first two queries.
In the complete log there are 40 queries for fetching 20 objects.

Quote:

net4j-thread-2 [debug] Reading list chunk values for feature Library.books of _YKYnkO0qEeaADtS-2Yy0SQv1
net4j-thread-2 [debug] Current chunk no. 0 is [start = 0, size = 20]
net4j-thread-2 [debug] Read value for chunk index 0 from result set: _YKZOoO0qEeaADtS-2Yy0SQ
net4j-thread-2 [debug] Read value for chunk index 1 from result set: _YKZOoe0qEeaADtS-2Yy0SQ
net4j-thread-2 [debug] Read value for chunk index 2 from result set: _YKZOou0qEeaADtS-2Yy0SQ
net4j-thread-2 [debug] Read value for chunk index 3 from result set: _YKZOo-0qEeaADtS-2Yy0SQ
net4j-thread-2 [debug] Read value for chunk index 4 from result set: _YKZOpO0qEeaADtS-2Yy0SQ
net4j-thread-2 [debug] Read value for chunk index 5 from result set: _YKZOpe0qEeaADtS-2Yy0SQ
net4j-thread-2 [debug] Read value for chunk index 6 from result set: _YKZOpu0qEeaADtS-2Yy0SQ
net4j-thread-2 [debug] Read value for chunk index 7 from result set: _YKZOp-0qEeaADtS-2Yy0SQ
net4j-thread-2 [debug] Read value for chunk index 8 from result set: _YKZOqO0qEeaADtS-2Yy0SQ
net4j-thread-2 [debug] Read value for chunk index 9 from result set: _YKZOqe0qEeaADtS-2Yy0SQ
net4j-thread-2 [debug] Read value for chunk index 10 from result set: _YKZOqu0qEeaADtS-2Yy0SQ
net4j-thread-2 [debug] Read value for chunk index 11 from result set: _YKZOq-0qEeaADtS-2Yy0SQ
net4j-thread-2 [debug] Read value for chunk index 12 from result set: _YKZOrO0qEeaADtS-2Yy0SQ
net4j-thread-2 [debug] Read value for chunk index 13 from result set: _YKZOre0qEeaADtS-2Yy0SQ
net4j-thread-2 [debug] Read value for chunk index 14 from result set: _YKZOru0qEeaADtS-2Yy0SQ
net4j-thread-2 [debug] Read value for chunk index 15 from result set: _YKZOr-0qEeaADtS-2Yy0SQ
net4j-thread-2 [debug] Read value for chunk index 16 from result set: _YKZOsO0qEeaADtS-2Yy0SQ
net4j-thread-2 [debug] Read value for chunk index 17 from result set: _YKZOse0qEeaADtS-2Yy0SQ
net4j-thread-2 [debug] Read value for chunk index 18 from result set: _YKZOsu0qEeaADtS-2Yy0SQ
net4j-thread-2 [debug] Read value for chunk index 19 from result set: _YKZOs-0qEeaADtS-2Yy0SQ
net4j-thread-2 [debug] Chunk finished
net4j-thread-2 [debug] Reading list chunk values done for feature Library.books of _YKYnkO0qEeaADtS-2Yy0SQv1
net4j-thread-4 [debug] Selecting revision _YKZOo-0qEeaADtS-2Yy0SQ from BranchPoint[Branch[id=0, name=MAIN], *]
net4j-thread-4 [debug] Selecting object type: _YKZOo-0qEeaADtS-2Yy0SQ
net4j-thread-4 [debug.sql] PreparedStatement[sql=SELECT CDO_CLASS FROM CDO_OBJECTS WHERE CDO_ID=?, probability=MAX, touch=17]
net4j-thread-4 [debug] Executing Query: PreparedStatement[sql=SELECT CDO_VERSION, CDO_CREATED, CDO_REVISED, CDO_RESOURCE, CDO_CONTAINER, CDO_FEATURE, TITLE, PAGES, AUTHOR, CDO_SET_PAGES FROM LIBRARY_SPECIALISTBOOK WHERE CDO_ID=?, probability=HIGH, touch=0]
...


Is there a configuration of the CDO server for fetching these 20 objects in one or two queries ?

First query return 20 objects from the table cdo_objects table, and the second query return 20 object from the Book table ?


Thanks for reply,
--
Philippe
Re: [CDO] -Performance - 2n queries for fetching n objects. [message #1753606 is a reply to message #1753523] Wed, 08 February 2017 07:41 Go to previous messageGo to next message
Eike Stepper is currently offline Eike StepperFriend
Messages: 6376
Registered: July 2009
Senior Member
CDO stores the object IDs for a list of objects. So, when you iterate over such a list of objects, CDO reads the object IDs in one query. For each ID CDO reads the object type with one query. and then the object itself is read with one query (plus one more query for each many-valued feature, unless partial collection loading is used, as in your case).

There's currently nothing to speed that up. But I'm working on an optimization to eliminate the object type query by encoding the object type directly in the object ID. Profiling shows that with embedded H2 the benefit is negligible (it's eaten up by additional costs to transfer bigger ID values). I expect bigger gains for remote DBs.
Re: [CDO] -Performance - 2n queries for fetching n objects. [message #1753620 is a reply to message #1753606] Wed, 08 February 2017 08:50 Go to previous messageGo to next message
Philippe EUGENE is currently offline Philippe EUGENEFriend
Messages: 37
Registered: January 2011
Member
Thanks for reply Eike.

For our current project, our need is to work with big model in a PostgreSQL database.
I need to evaluate if this design is a potential performance bottleneck for our differents use cases.

[Updated on: Wed, 08 February 2017 08:51]

Report message to a moderator

Re: [CDO] -Performance - 2n queries for fetching n objects. [message #1753640 is a reply to message #1753620] Wed, 08 February 2017 11:44 Go to previous messageGo to next message
Eike Stepper is currently offline Eike StepperFriend
Messages: 6376
Registered: July 2009
Senior Member
Interesting. I started this new year with a couple prototypes of performance optimizations and I have a few more in mind. If you find specific bottlenecks it would be great to get that feedback...
Re: [CDO] -Performance - 2n queries for fetching n objects. [message #1753654 is a reply to message #1753640] Wed, 08 February 2017 14:19 Go to previous messageGo to next message
Philippe EUGENE is currently offline Philippe EUGENEFriend
Messages: 37
Registered: January 2011
Member
Our target model is under postgreSQL with around 2.000.000 of EObject.
The meta-model contains around 1.000 EClass.

For 90% of our Use cases, CDO fullfilled our needs.

I can fetch few objects to the UI using the two options :
- CDOCollectionLoadingPolicy
- RevisionPrefetchingPolicy

For 10% of our Use case, we need fetch more than 10.000 objects to the UI.

For test purpose, i use the Library model with :
- 1.000 Library
- and 2.000 Book by Library.

Using CDO, we can fetch 1.000 EObject per second. We must therefore ten seconds for fetch 10.000 EObjects. This time is too long for open a view in the UI.

Without CDO, if i test a single SQL query i can fetch 10.000 objects in less than 500 ms.

SELECT b.*
from library_library as l
  INNER JOIN library_library_books_list as lb ON l.cdo_id = lb.cdo_source
  INNER JOIN library_guidebook as b on lb.cdo_value = b.cdo_id LIMIT 10000;


Reduce the number of queries to the database could be a way to improve CDO performances.

Re: [CDO] -Performance - 2n queries for fetching n objects. [message #1753668 is a reply to message #1753654] Wed, 08 February 2017 16:42 Go to previous message
Erdal Karaca is currently offline Erdal KaracaFriend
Messages: 840
Registered: July 2009
Senior Member
Just a hint:
If you do not need EObjects/CDOObjects in the UI, you can also execute native queries (as in your example) and return the result as a list of Maps,
then load the real object on first access, for example, in a master details scenario.

Philippe EUGENE wrote on Wed, 08 February 2017 15:19
Our target model is under postgreSQL with around 2.000.000 of EObject.
The meta-model contains around 1.000 EClass.

For 90% of our Use cases, CDO fullfilled our needs.

I can fetch few objects to the UI using the two options :
- CDOCollectionLoadingPolicy
- RevisionPrefetchingPolicy

For 10% of our Use case, we need fetch more than 10.000 objects to the UI.

For test purpose, i use the Library model with :
- 1.000 Library
- and 2.000 Book by Library.

Using CDO, we can fetch 1.000 EObject per second. We must therefore ten seconds for fetch 10.000 EObjects. This time is too long for open a view in the UI.

Without CDO, if i test a single SQL query i can fetch 10.000 objects in less than 500 ms.

SELECT b.*
from library_library as l
  INNER JOIN library_library_books_list as lb ON l.cdo_id = lb.cdo_source
  INNER JOIN library_guidebook as b on lb.cdo_value = b.cdo_id LIMIT 10000;


Reduce the number of queries to the database could be a way to improve CDO performances.


Previous Topic:DefaultDetailComposite related dependencies
Next Topic:Constructor with parameters?
Goto Forum:
  


Current Time: Fri Apr 20 03:28:46 GMT 2018

Powered by FUDForum. Page generated in 0.01510 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software