Re: [sqlite] A list as an SQL paramater

[Clark Christensen]

> 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