| [CDO] -Performance - 2n queries for fetching n objects. [message #1753523] |
Tue, 07 February 2017 12:47  |
 Philippe EUGENE
Messages: 58
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  |
|
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.
Cheers
/Eike
----
http://www.esc-net.de
http://thegordian.blogspot.com
http://twitter.com/eikestepper
|
|
|
|
|
|
| Re: [CDO] -Performance - 2n queries for fetching n objects. [message #1753668 is a reply to message #1753654] |
Wed, 08 February 2017 16:42 |
Erdal Karaca
Messages: 854
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:19Our 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.
|
|
|
Powered by
FUDForum. Page generated in 0.03022 seconds
] 
Search
Help
Register
Login
Home
