On 10 sep. 2013, at 21:24, "E.Pasma" <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. Your suggestion that when preparing a DELETE statement, the global list of FK's is scanned to see if the current table is referred seems to be wrong for what we could find. >From sqlite3FkActions the method sqlite3FkReferences(pTab) is called; this >method uses a hash table (pTab->pSchema->fkeyHash) to know immediately which >FK's are referring to the given table, without having to loop through a global >list of FK's. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
