Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Modeling » EMF » Optimizing AuditListTableMappingWithRages.moveUp/Down
Optimizing AuditListTableMappingWithRages.moveUp/Down [message #1850417] Wed, 02 March 2022 10:20 Go to next message
Linuxhippy Mising name is currently offline Linuxhippy Mising nameFriend
Messages: 71
Registered: July 2009
Member
Hi,

Like a few years ago I experience issues with performance of CDO managed lists when deleting the elements of large lists. Instead of performing batch-updates, CDO emits ahuge number of single-line-changing SQL statements.

When e.g. taking a look at moveOneUp(), it executes the following statement for each affected row:
UPDATE table SET list_idx=? WHERE list_revision_id=? AND list_version_added=? AND list_idx

My first idea was to do this simply with all rows at once:
UPDATE  table SET list_idx=list_idx-1 WHERE  list_revision_id=? AND list_version_added=? AND list_idx IN (......)


However, I then discovered the case where the Update reports no changed rows which triggers a remove/add pair.

I wonder - in which cases a regular update is sufficient, and in which cases the remove/add pair is needed?

    for (int index = startIndex; index <= endIndex; ++index) {
       ....
          int result = DBUtil.update(stmt, false);
          switch (result)
          {
          case 0:
            Object value = getValue(accessor, id, index);
            removeEntry(accessor, id, oldVersion, newVersion, index);
            addEntry(accessor, id, newVersion, index - 1, value);
            break;
         case 1:  // as expected, update went through


Thank you in advance, Clemens
Re: Optimizing AuditListTableMappingWithRages.moveUp/Down [message #1850423 is a reply to message #1850417] Wed, 02 March 2022 13:40 Go to previous messageGo to next message
Linuxhippy Mising name is currently offline Linuxhippy Mising nameFriend
Messages: 71
Registered: July 2009
Member
I am one step further, it seems the first update is only there to move "temporary" objects which do not have to be versioned.

So this is the current idea I came up with for moveOneUp:
CDO_LIST table = V1_CDORESOURCE_CONTENTS_LIST
newVersion = 79999
index old = 34623
index new = 34622
CDO Source = 499819

// 1. get all temporary objects which would be affected
select CDO_IDX  from V1_CDORESOURCE_CONTENTS_LIST where CDO_SOURCE=499819 and CDO_VERSION_ADDED=79999 and  CDO_IDX IN ( indexes generated by for-loop )

//2. for all temporary objects (result of query in 1.)
update V1_CDORESOURCE_CONTENTS_LIST set CDO_IDX=CDO_IDX-1 where CDO_SOURCE=499819 and CDO_VERSION_ADDED=79999 and cdo_idx IN (result of query in 1.) 

// Delete temporary object with the old index is present in the current code - but how can this be possible? In case such an entry existed, we've already updated its CDO_IDX column. Are there any cases where executing sqlDeleteEntry in removeEntry() might affect any rows when called from moveOneUp()?
//DELETE FROM V1_CDORESOURCE_CONTENTS_LIST WHERE CDO_SOURCE=499819 AND //CDO_IDX=34623 AND CDO_VERSION_ADDED=79999

// 3. mark all non-temporary objects affected as removed (all idx generated by the loop which were not returned by the query of step 1.)
UPDATE V1_CDORESOURCE_CONTENTS_LIST SET CDO_VERSION_REMOVED=79999 WHERE CDO_SOURCE=499819 AND CDO_VERSION_REMOVED IS NULL AND CDO_IDX IN (...)

// 4. insert the new entries (in JDBC batch mode)
INSERT INTO .....

// 5. call typeMapping.setValue() accordingly



Are there any serious shortcomings with this approach with gegards to CDO internals / internal assumptions?

Thanks & best regards, Clemens
Re: Optimizing AuditListTableMappingWithRages.moveUp/Down [message #1850444 is a reply to message #1850423] Thu, 03 March 2022 09:29 Go to previous message
Linuxhippy Mising name is currently offline Linuxhippy Mising nameFriend
Messages: 71
Registered: July 2009
Member
Thinking it through a bit more, I guess moving to batch-style database operations won't solve the actual scalability issue.
Lists that perform poor without those optimizations (because of all those blocking round-trips to the DB-Server) will get a significant speed bump - however, the other issue might be space efficiency.
I guess space-complexity of random insert/remove operations of those ordered lists is (n^2)/2.

Just to be curious, does CDO support unordered lists / bags?

Best Regards

[Updated on: Thu, 03 March 2022 09:30]

Report message to a moderator

Previous Topic:[CDO] NPE in AbstractQueryIterator.close(AbstractQueryIterator.java:88)
Next Topic:Set-accessor Body?
Goto Forum:
  


Current Time: Thu Mar 28 14:31:15 GMT 2024

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

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

Back to the top