Indexing Suggestions / Code Slows Down Midway


I’m using the nested set model to store categories. I’ve narrowed down where my processing slows to the code below.

I process a file that contains about 20,000 category records. For record 1 to about 10,000, the Update code below takes about 17 milliseconds to execute. At about record number 10,000 this time slows to about 35 milliseconds and at approximately record 15,000 it slows to a crawl of 50 milliseconds.

I realize that as the file processes there are more nodes of the tree to update, but I would like to try and optimize this as much as possible.

Can I get this any faster? What’s the best way to set up indexes for this table and is it a good idea to drop the indexes before I process the file?

"UPDATE Tree SET " + _ "tree_left = " + _ "CASE WHEN tree_left > " + str(v_parent_rht) + " THEN " + _ "tree_left + 2 " + _ "ELSE tree_left " + _ "END," + _ "tree_right = " + _ "CASE WHEN tree_right >= " + str(v_parent_rht) + " THEN " + _ "tree_right + 2 " +_ "ELSE tree_right END " + _ "WHERE tree_right >= " + str(v_parent_rht)+ " " + _ "AND tree_location_id = " + v_location_id

Thank you

What database engine ?

More often than not it is a good idea to drop indexes then rebuild later

Might a transaction help here?

The big problem with nested sets is just they do generate a lot of thrashing on insert as you constantly update things
With indexes still set up that can make for a LOT of reindexing etc

Maybe precalculate the values in memory rather than thrashing the disk? Load up your data into a data structure, then write it all out.

[quote=65998:@Norman Palardy]What database engine ?
More often than not it is a good idea to drop indexes then rebuild later[/quote]
I’m using the built in SQLite. Strangely, I just tried dropping the indexes before processing and there’s really no difference in time.

I should have mentioned that this code is already in a transaction.

So you’re saying, make a class with all columns of the tree table. Load the whole table into the class at one time and exchange this SQL Update code for XOJO? Would I overload the memory? The file I process is typically about 5 MB and I also have it loaded up in memory using LoadXML.

5MB is small so I doubt you’d have issues doing it all in memory
You could just use an in memory sqlite database then when everything is set up write it to an on disk database