-----Original Message----- Date: Tue, 10 Sep 2013 15:15:35 +0000 From: Harmen de Jong - CoachR Group B.V. <har...@coachr.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] Query preperation time does not scale linearly with growth of no. of tables Message-ID: <c06aa165-6904-43f6-8d33-6a0f2c99f...@coachr.com> Content-Type: text/plain; charset="us-ascii"
[snip] That [memory being re-allocated] is something we suspected too. We already made some tests where we timed the time needed for all memory allocations executed in the entire operation. In total for the 10000 tables test this was somewhere around 25 msec. Since this is just a little overhead and the instructions as you point out have a linear increasement this still does not explain the polynomial increasement in preperation time. [J. Merrill's comment below] When there's a re-allocate, it's not just the time to allocate the new memory -- there's the time to copy the data from the original not-big-enough location to the new big-enough-for-now location. The amount of data moved grows rapidly in that case with each re-allocation, and the pattern of extra-time seems similar to the data-movement-growth when there are repeated allocate/move steps. How many re-allocations happen? Do you know by how much the allocation size increases each time a re-allocation is needed? (A common algorithm is to double the size each time.) You might try changing the source code so that it triples or quadruples the size each time, as memory does not seem to be an issue. Another point -- I did not see you comment on the possibility that you could remove the FK specs from the 10,000 tables. Do you really have the situation that deletions from the main table would "orphan" rows in an unknown number of other tables, and the existence of those orphan rows would in fact cause application failures? (If the rows were not deleted from the other tables, using a normal join to the main table -- rather than a left join -- in the queries would make those rows disappear.) Alternatively you could create a table to hold the ids of the rows deleted from the main table, and build a separate process -- to be run as often as desired -- to do delete from onechildtable where id in (select id from deletedids) (That SQL could also be of the form delete from onechildtable where id in (5,6,9,12,999) should you determine that there aren't very many deleted ids.) You would do that for each of the other tables -- this is exactly the work that SQLite is preparing to do when you prepare a "delete from maintable" statement. When done deleting from all the tables, you could remove all the rows from the deletedids table. (You'd have to do something to block deletions from the main table -- thus blocking insertion into the deletedids table -- during the process, and of course you would start the process with "do nothing if deletedids has no rows".) This would just have the effect of batching together multiple main-table deletes before going through every child table for the purpose of deleting orphans. I would not suggest the "delete in batches" idea except that it's clear that you have a major collection of infrastructure set up to handle this unusual task. I don't think that what I'm suggesting would add a huge new component to that infrastructure. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users