Many thanks Drake, all of your points were highly pertinent. I'll
stop lazily replying to threads and changing the subject!
I indeed see that my approach was pretty bafflingly bad in highsight. I
tend to do most "logic" in the scripting language as opposed to in
SQLite commands as it's what I'm comfortable with, but I probably need
to think more in terms of what SQLite can automate for me.
Your query,
UPDATE userTable SET playedInfIds = ''
Still took two seconds actually... but significantly better than what I
was doing.
Ian
On 05/10/2010 12:08, Drake Wilson wrote:
> 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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
