Dennis Cote schrieb:
> Michael Schlenker wrote:
>> Or would i have to create a dynamic query with appropriate number of bind
>> variables myself and have to pay the price of reparsing each time?
>>
>> # like this
>> values = [uuid1,uuid2]
>> binders = ",".join("?"*len(values))
>> cursor.execute("SELECT * FROM t WHERE uuid IN (%s)" % binders, values)
>>
>
> If you are going to prepare the query each time, why not embed the
> values directly in the query string and avoid all the bind calls? There
> is no benefit to binding these values unless they are possibly user
> supplied strings that could be used for an SQL injection attack. If they
> are generated by your application it would be faster and simpler to skip
> that step.
No, i don't prepare it each time, (the wrapper caches some of the statements
internally, via hashes).
I now got it working with a pivot table, which worked nice because i have
fixed length ids and it looks like this now:
buffer = "".join((b.bytes for b in uuids))
length = len(blobs)
ldb = db.execute("""
SELECT meta.id, min(
CASE
WHEN meta.deleted = 1 THEN 'del'
WHEN location.location_id = ? THEN 'loc'
ELSE 'non'
END) as status
FROM meta
LEFT OUTER JOIN location
ON meta.id = location.item_id
WHERE meta.id IN
-- iterate over binary buffer, each uuid takes
-- 16 bytes
(SELECT substr(?,(n-1)*16,n*16) FROM pivot WHERE n <= ?)
GROUP BY meta.id
ORDER BY status""", (1, buffer, length))
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.
Thanks for the good suggestions. I might have a look at creating a temp
table first, but doubt it will be faster.
Michael
--
Michael Schlenker
Software Engineer
CONTACT Software GmbH Tel.: +49 (421) 20153-80
Wiener Straße 1-3 Fax: +49 (421) 20153-41
28359 Bremen
http://www.contact.de/ E-Mail: [EMAIL PROTECTED]
Sitz der Gesellschaft: Bremen
Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
