Quoth Ian Hardingham <i...@omroth.com>, on 2010-10-05 11:52:36 +0100: > Hey guys. My apologies in advance if this is a slightly mundane question.
(Please don't start new threads by replying to random messages. The resultant header information indicates falsely that your email is part of the same thread.) > I'm running this code from a scripting language bound to SQLite: > > %r = db.query("SELECT * FROM userTable", 0); > %i = 0; > > db.query("BEGIN TRANSACTION", 0); > while (%i < db.numRows(%r)) > { > %username = db.getColumn(%r, name); > db.query("UPDATE userTable SET playedInfIds='' WHERE name LIKE > '?'", 0, %username); > %i ++; > } > db.query("END TRANSACTION", 0); Ah-heh? A number of points come to mind fairly immediately: - Don't keep a query from outside a transaction active inside it. - Don't SELECT * when all you need is one column. - You shouldn't have to iterate a result set by numerically iterating until you hit the total number of rows, but I don't know what API this is, so I don't know exactly how the replacement would look. - This whole loop looks like it could be replaced with the single query « UPDATE userTable SET playedInfIds = '' » because you're just targeting all the rows, unless there's something unobviously different that I've missed. Right now you're doing a full table scan to get each name, then doing another full table scan for each name to update each row with a similar name. That's O(N^2) in the number of rows; with 3k rows, that requires ~9M processing steps. > Is there anything obvious I'm doing wrong? I know using LIKE is not > ideal, but the scripting language does not guarantee case so it is > necessary here. Store the name in a canonical form (e.g., all lowercase) in the database, then query based on that form. You can store the non-canonical form next to it in a separate column if it's needed. The fact that you are using LIKE suggests that 'ian' and 'Ian' should be treated identically, but currently your primary key allows separate rows to exist for each of those. Also, PRIMARY KEY UNIQUE is redundant. A primary key is always unique. ---> Drake Wilson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users