Hi Here are the results of my speed testing: Recompile: 85.937500 total, 0.017188 avg. Temporary table: 87.000000 total, 0.017400 avg. Virtual table: 85.562500 total, 0.017112 avg.
As you can see the whole thing was really a pointless exercise :( I had imagined the impact of 'prepare' to be much higher than it actually is - using a virtual table seems to be generally a tiny bit faster, but we are talking .4 of a second over 50,000 calls, when I don't see more than a few hundred being done at a time. Bearing this in mind I will stop wasting my time worrying about such silly performance issues :) Thanks for all the input in this thread I have at least learned a bit about sqlite! PS. if anyone is interested, the (shabby) code I used to find these numbers is here: http://dl.getdropbox.com/u/407072/junk/sqlite-test.c Sam. On Wed, Jan 14, 2009 at 9:26 PM, Sam Thursfield <sss...@gmail.com> wrote: > On Wed, Jan 14, 2009 at 4:38 PM, Igor Tandetnik <itandet...@mvps.org> wrote: >> Sam Thursfield <sss...@gmail.com> wrote: >>>>> - binding text results in SELECT * ... WHERE foreign.id IN ("4. 3, >>>>> 6, 7") ...; which of course doesn't work. >>> >>> Do you know off hand whether this method would be much faster than >>> compiling a new query for each new list of ids? >> >> I'm not sure I understand the question. How can one compare the speed of >> the solution that doesn't work with one that does? >> > > What I'm comparing is the speed of SELECTing the list from a temporary > table, vs. building a new query string with printf and calling > sqlite3_prepare each time I want to execute it with a different $list. > This would definitely work but does incur the overhead of > sqlite3_prepare each time I want to run the query. > > I had an idea for a third method too, which would use a virtual table > instead of a temporary table, and get the list from the app to to the > query that way. I think I'm going to do some profiling to found out > which method is the fastest. > > MikeW, that's an ingenious idea but I have slightly more than 64 rows :) > > Sam > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
