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
