On 10 sep. 2013, at 21:24, "E.Pasma" <pasm...@concepts.nl<mailto:pasm...@concepts.nl>> wrote:
My suppositions that the time was spent in the execute step and that this has been fixed in the new release appeared both wrong. Thus I may be wrong again but I think to have an explanation now. It is as Simon guesses that a list of lists is being scanned. It is however not the contents of tables being scanned, but the list of foreign key constraints as is loaded in memory when a database is opened. When preparing a DELETE statement, the global list of FK's is scanned to see if the current table is referred. This is the outer loop. If a referring FK is found and if this has an ON DELETE clause, comes an inner loop on the same global list to see if the referrer is referred to itself. In the case that every table has such a constraint, as is the case here, the time becomes n * n. If I'm right this is hard to fix and inherent to the representation of the database schema in memory. This also means that if you leave out the cascading delete from the constraints the time becomes linear. Actually that is what I observed before coming with above explanation. This was easy to check by extractingg the schemas from the test databases and removing ON ...... CASCADE. Thanks for making these database available. To get rid of the question of WHERE exactly the time is consumed, we did some profiling on the application that run the query (using the 10000 tables test DB). As a result you will find an overview of time consumed per function (shown as percentage of the total time) at this link: http://www.coachrdevelopment.com/share/callstack_tree.html This shows most time is spend on sqlite3CodeRowTriggerDirect. Now the question remains IF this function is causing the polynomial increase in time and if so, WHY it causes a polynomial increase in time and if there are any optimizations possible. We don't see it yet. Looking forward to any suggestions. Best regards, Harmen _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
