On Oct 14, 2008, at 4:26 PM, Peter van Hardenberg wrote:
> Hi all,
>
> We're using SQLite to back the media collection in Songbird, and our
> database includes a table which often has a varying set of columns
> updated. At the moment, I'm using a set of prepared statements that
> look like this:
>
> UPDATE table SET columnN = ? WHERE table_id = ?;
>
> I talked with Habbie in IRC (who recommended I post to the list) and
> he advised trying a query like this:
>
> UPDATE table SET
> column1 = CASE 1=? THEN ? ELSE column1 END,
> column2 = CASE 1=? THEN ? ELSE column2 END,
> column3 = CASE 1=? THEN ? ELSE column3 END
> WHERE table_id = ?;
>
> This would theoretically allow me to prepare a single query once and
> to simply bind a "1" and a value to each property I want to change
> with the rest staying the same. Unfortunately, the query executes
> very slowly, about an order of magnitude slower than simply piecing
> together the query as a string each time and compiling it from
> scratch.
I tried the experiment on Linux. I used:
CREATE TABLE t1(a,b,c,d);
-- insert over 1000 rows of data.
UPDATE t1 SET
a = CASE WHEN 1=$ax THEN $av END,
b = CASE WHEN 1=$bx THEN $bv END,
c = CASE WHEN 1=$cx THEN $cv END,
d = CASE WHEN 1=$dx THEN $dv END
WHERE
rowid=$rid
where the bx, bv, cx, cv, dx, and dv values were all unbound and thus
understood as NULL. I prepared the statement once and reused it to do
1000 inserts. The average time was 26.058 milliseconds. Then I did:
UPDATE t1 SET a=$av WHERE rowid=$rid
I prepared the statement separately 1000 times. The average time was
55.458 milliseconds.
Everything was done inside a single transaction.
D. Richard Hipp
[EMAIL PROTECTED]
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
