Re: [sqlite] Efficient updating of arbitrary columns.

[D. Richard Hipp]

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

[sqlite] Efficient updating of arbitrary columns.

[Peter van Hardenberg]

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.

On IRC, Habbie expressed some surprise that it was so slow. While I'm no expert 
with analyzing the VM output, I suspect SQLite is making some suboptimal 
decisions about how, or how often, to pull the data required to execute the 
query. Any suggestions on how to improve the performance of this query, or 
another one like it?

Thanks,
-pvh
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users