Optimizing AuditListTableMappingWithRages.moveUp/Down [message #1850417] |
Wed, 02 March 2022 10:20 |
Linuxhippy Mising name 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 #1850444 is a reply to message #1850423] |
Thu, 03 March 2022 09:29 |
Linuxhippy Mising name 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
|
|
|
Powered by
FUDForum. Page generated in 0.01836 seconds