Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Modeling » EMF » [CDO] Inefficient SQL generated for CDORepository inserts/removes
[CDO] Inefficient SQL generated for CDORepository inserts/removes [message #1792170] Thu, 12 July 2018 10:03 Go to next message
Linuxhippy Mising name is currently offline Linuxhippy Mising nameFriend
Messages: 14
Registered: July 2009
Junior Member
Hi,

We are experiencing servere performance issues when inserting / removing Objects from a rather small (~1500 entries) CDORepository which is backed by an Oracle database.

It seems to keep ordering, CDO generate a huge amount of SQL statements that are executed non-batched (~3 for each CDO object contained in the Repository) - adding a single Object at the beginning of the Repository causes 4500 sql statements to be generated, multiplied with ~15ms round trip latency this results in over a minute the modification takes.
This is caused by the moveOneUp/moveOneDown methods called by AuditListTableMappingWIthRanges$ListDeltaVisitor.

This was observed when using cdo.server.db.4.4.0.v20160607-1254, but looking at the latest CDO code those methods seem to be more or less working the same way.

Is there any way to make inserts/removes to/from a CDORepository more efficient with a database store?

Thank you in advance & best regards, Clemens

[Updated on: Thu, 12 July 2018 10:05]

Report message to a moderator

Re: [CDO] Inefficient SQL generated for CDORepository inserts/removes [message #1792253 is a reply to message #1792170] Fri, 13 July 2018 09:32 Go to previous messageGo to next message
Eike Stepper is currently offline Eike StepperFriend
Messages: 6394
Registered: July 2009
Senior Member
Linuxhippy Mising name wrote on Thu, 12 July 2018 12:03

It seems to keep ordering,


Yes, EMF's many-valued features are generally implemented with Lists, which are ordered. If you define such a feature as "Ordered=false" in the Ecore model, CDO uses a slightly optimized org.eclipse.emf.internal.cdo.CDOObjectImpl.CDOStoreUnorderedEList, see org.eclipse.emf.internal.cdo.CDOObjectImpl.createList(EStructuralFeature).

Linuxhippy Mising name wrote on Thu, 12 July 2018 12:03

CDO generate a huge amount of SQL statements that are executed non-batched (~3 for each CDO object contained in the Repository)


It's possible that there's room for optimization, but I'd need to know exactly what operations/modifications you've done.

Linuxhippy Mising name wrote on Thu, 12 July 2018 12:03

- adding a single Object at the beginning of the Repository causes 4500 sql statements to be generated, multiplied with ~15ms round trip latency this results in over a minute the modification takes.
This is caused by the moveOneUp/moveOneDown methods called by AuditListTableMappingWIthRanges$ListDeltaVisitor.


By "at the beginning of the Repository" you mean "into a new, empty repository"? Then, what are those 4500 statements? A single, new object should not cause any moveOneUp/Down() calls. Are they "CREATE TABLE" statements? How big is your meta model? What is this object? Does it have many-valued features with many elements? I need more details (ideally an executable test case to reproduce the scenario) to properly comment...

Linuxhippy Mising name wrote on Thu, 12 July 2018 12:03

This was observed when using cdo.server.db.4.4.0.v20160607-1254, but looking at the latest CDO code those methods seem to be more or less working the same way.

Is there any way to make inserts/removes to/from a CDORepository more efficient with a database store?


Indeed it seems that AuditListTableMappingWIthRanges$ListDeltaVisitor might profit from statement batching. Are you interested in contributing a respective patch? NonAuditListTableMapping$ListDeltaWriter already seems to employ batching.
Re: [CDO] Inefficient SQL generated for CDORepository inserts/removes [message #1792376 is a reply to message #1792253] Mon, 16 July 2018 15:28 Go to previous messageGo to next message
Linuxhippy Mising name is currently offline Linuxhippy Mising nameFriend
Messages: 14
Registered: July 2009
Junior Member
Hi Ralf,

Quote:
By "at the beginning of the Repository" you mean "into a new, empty repository"?


I mean inserting an element at e.g. position 5 into a Repository already containing with 1500 elements (so moving is required).
At least for my use-case moveOneDown is called (to make space for the new element at the specified index, which was occupied before) - moveOneDown in my case:
1. Tries ot update, which returns 0 -> no element matched
2. loads the value from the DB (getValue)
3. removeEntry -> 2 SQL staments (remove + update)
4. addEntry (insert)

Without batching all those steps are performed synchronously with the DB server. So while it might be great scalability wise to be able to change ordering of a 1 nbillion-element-list without loading into memory, it would take almost forever.

I currently solved it by moving all the affected entries from oracle into an in-memory database (hsqldb), run the current code as is and later re-insert it into oracle.
This helped to speed up moveOneDown/Up from 80s to ~300ms.

Maybe I can contribute a proper implementation without using an in-memory intermediate database.






Re: [CDO] Inefficient SQL generated for CDORepository inserts/removes [message #1793011 is a reply to message #1792376] Fri, 27 July 2018 13:10 Go to previous message
Eike Stepper is currently offline Eike StepperFriend
Messages: 6394
Registered: July 2009
Senior Member
I think an intermediate in-memory database is no option for CDO. I would first try to apply stement batching similar to NonAuditListTableMapping.ListDeltaWriter. I'm currently not sure how the calls AuditListTableMappingWithRanges.getValue() would interfere with a batching approach, but it would be great if you could try it out. Let me know if you need further help.

If batching alone isn't enough, we could discuss possible other/additional options.

BTW., who is Ralf?

Previous Topic:[CDO] Queries related to Standalone Client - Reg.
Next Topic:[CDO] Individual access rights for many resources
Goto Forum:
  


Current Time: Wed Sep 26 10:20:25 GMT 2018

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

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

Back to the top