[whatwg] Comments on updated SQL API

[Scott Hess]

On 10/17/07, Brady Eidson <beidson at apple.com> wrote:
> Assuming using sqlite for the back end, I just wrote a quick little
> driver that creates a table with 10 columns, then inserts the exact
> same value into the table 20,000 times.
> I then ran the exact same test that does the exact same thing, but
> wraps each individual insert in a transaction.
>
> The transaction case is 5% slower.

But in this case, if you inserted the values 1,000 per transaction, it
would probably be 10x faster.  Maybe 100x faster if you're dealing
with a network filesystem.

The performance case for not using implicit transactions for server
databases is that it can allow for more concurrency.  If the client
sends a statement to the server without an enclosing transaction, the
server can minimize the amount of time the transaction has the
database/table/row locked.  If the client has to open the transaction,
that means a minimum of two additional round trips back to the client
are introduced (and much worse, if either the client or server are
very busy).

For an embedded database like SQLite, things are different.  In that
case, no matter what, you're going to pay a big cost for fsync.
Making the transaction explicit will have an impact, but I'm really
surprised that you're seeing 5%.  I would bet that you're doing BEGIN
rather than BEGIN IMMEDIATE, which means that your 5% is probably down
to upgrading your database locks.  If so, that can be worked around by
implementing the spec using BEGIN IMMEDIATE rather than BEGIN
DEFERRED.

For the current spec, concurrency isn't a huge issue, because
everything will be serialized at some level anyhow.  Also, a trick
like Firefox uses for it's use of SQLite may be in order (file
operations are bridged to a background thread to make things async -
losing durability).  If something like a Gears WorkerPool is
introduced, then this potentially becomes more of an issue.

-scott

[Sorry, don't mean to sound like I'm flip-flopping.  My concerns about
implicit transactions aren't really performance related. :-).]