Eclipse Community Forums: EMF » [CDO] -Performance - 2n queries for fetching n objects.

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 07:47

Eclipse User

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 02:41

Eclipse User

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 03:50

Eclipse User

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 03:51] by Moderator

Re: [CDO] -Performance - 2n queries for fetching n objects. [message #1753640 is a reply to message #1753620]

Wed, 08 February 2017 06:44

Eclipse User

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 09:19

Eclipse User

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 11:42

Eclipse User

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:

-=] Back to Top [=-

Current Time: Wed Jul 23 18:32:44 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter