On Tue, Oct 05, 2010 at 04:08:41AM -0700, Drake Wilson scratched on the wall:
> Quoth Ian Hardingham <i...@omroth.com>, on 2010-10-05 11:52:36 +0100:
> > 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);
> A number of points come to mind fairly immediately:
>
> - Don't keep a query from outside a transaction active inside it.
While this is good advice, I don't think that's what is going on
here. The SELECT should definitely be inside the BEGIN/END, because
otherwise the data may change between the SELECT and UPDATE loop, but
that's a different issue. If I understand this scripting API, the
query() function will run the SELECT to completion, so any auto-commit
transaction that is open will be shut again by the time the code
enters the loop.
Not that it matters... If the SELECT did open an auto-commit
transaction that was not closed, a transaction would already be open
and the BEGIN/END wouldn't change that. The whole collection of
statements would be run inside the auto-commit transaction until
all the statements were complete.
> - 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.
Yes... usually the mistake is to forget the WHERE, but in this case
it looks like the OP actually wants to change every row in the table,
and that can be done with a single UPDATE statement.... just drop the
WHERE all together.
That column name makes me suspicious, however... I have a bad feeling
that's a text field that is made up of delineated values,
essentially forming a list. If true, that's a very poor design.
> 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.
It isn't that bad. He's looking up a PRIMARY KEY without wildcards
(I assume), so each look-up is O(logN), making the total O(NlogN).
It is a bloated O(logN) because you're depending on the LIKE
optimizations, but it is still technically O(logN). Not good for
the desired results, which should more or less be O(N), but not
nearly as bad as O(NN).
> Also, PRIMARY KEY UNIQUE is redundant. A primary key is always
> unique.
True, but it does no harm. SQLite is smart enough to only create one
index. The NOT NULL would be redundant in most database systems as
well, but SQLite requires it for true PK behavior.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
