Michael Schlenker wrote:
>
> I now got it working with a pivot table, which worked nice because i have
> fixed length ids
>
> (SELECT substr(?,(n-1)*16,n*16) FROM pivot WHERE n <= ?)
Michael,
That's a nice trick. I'll have to remember that.
> Basically i have to check a larger number (might be around 10.000 or more,
> but usually far less) of ids against my (incomplete) metadata catalogue to
> find out if those ids are locally available or need to be fetched from a
> remote (slow) location.
>
Since you have to handle up to 10K uuids in the string, your pivot table
must have that many rows. To ensure SQLite doesn't waste time doing a
full table scan on pivot table you should make column n an integer
primary key, or you can simply use a limit clause instead of the comparison.
(SELECT substr(?,(n-1)*16,n*16) FROM pivot LIMIT ?)
> Thanks for the good suggestions. I might have a look at creating a temp
> table first, but doubt it will be faster.
The subselect you are executing already creates a temp table (well
really a temp index) and uses that to select rows in the meta table. I
don't think you can do it any faster than SQLite is doing for the
subquery. There is probably no benefit unless you want to reuse the temp
table for several meta table queries.
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
