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
