> 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.
Please forgive me if I'm missing a key point, but it sounds like you already have the list in a temp table. If that's the case, wouldn't something like SELECT * ... WHERE foreign.id IN (select foreign_id from temptable...) do the job for you? FWIW, I often find myself wanting to pass a list (array) of variable length as bound parameters. I understand why it may not be practical, but it would be a nice feature. Is this even part of the SQL standard? -Clark ----- Original Message ---- From: Sam Thursfield <sss...@gmail.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Wednesday, January 14, 2009 1:26:14 PM Subject: Re: [sqlite] A list as an SQL paramater 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users