Tito Ciuro wrote:
Assume the following scenario: I store people in a table, like this:
People
ROWID, idx
GUID, idx
First, idx
Last, idx
Email
...
In the app, the user can select People GUIDs from different sources
and then retrieve the info from the database. The easy/suboptimal
route to retrieve the records would be to perform a SELECT per GUID
selected. I thought of something like this:
SELECT * FROM People where GUID in ("ABC", "RDT", "TUV");
Is there a better way to include all these GUIDs on a single SQL
statement to speed things up?
Questions I have:
1) Is this the best way to solve the problem? Suggestions?
Tito,
You might want to use a temp table to hold your list of GUIDs. Then you
can use a single precompiled query to do the select.
select * from People where GUID in (select GUID from temp_guids)
As it is, you will have to prepare your query for each lookup. The temp
table would replace the loop that binds the parameters with a loop that
inserts the guids into the temp table.
Also, you are using double quotes for string literals, these should be
single quotes instead.
... in ('ABC', 'RTD', 'TUV')
2) Is there a limit on the number of parameters I can pass to "in"?
What if I have, say, 500 to retrieve? Will SQLite complain about this?
As far as I know there is no hard limit on the number of parameters you
can use in a query. If you want to precompile the query you will need to
have as many as your maximum requirement. In that case you will have to
bind all the parameters before the query, and then reset them to null
after the query so that the values are reset for the next query (i.e you
will have to bind each variable twice). If you don't do this a query
with many parameters followed by a query with a few will still have
values bound to the higher numbered parameters left over from the first
query.
In the alternate, temp table approach, you would create the table if it
doesn't exist
execute("begin")
execute("create temp table if not exists temp_guids(guid primary key)")
Then loop to insert the GUIDs using a precompiled insert with one parameter
s = prepare(insert into t values(?))
for each guid in guid_list:
bind(s, 1, guid)
execute(s)
execute ("commit")
Now execute your guid lookup query
people = execute("select * from People where GUID in (select guid
from temp_guids)"
And finally clear the temp table
execute("delete from temp_guid")
Of course you would have to try each methods to see which is fastest.
The single query requires compilation for each lookup, a single
precompiled query requires binding each variable twice but has the
advantage that the VDBE code will build an index from the parameters on
the fly (without an associated table), the temp table approach can be
done by executing only prepared sql statements but will build two
records (one in the table and one in the index) for each record.
I seem to be rambling so I will stop now.
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
