[whatwg] Comments on updated SQL API

[Brady Eidson]

On Oct 17, 2007, at 11:04 AM, Scott Hess wrote:

> 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.

I agree completely.  The debate is not whether transactions speed up  
batch queries.  It's whether they slow down individual queries - which  
I have evidence saying they do.
My point is that if we can all end up agreeing it is a performance  
hit, then it is an agreed upon mark against the *implicit* transaction.

> 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).

I'm also concerned about this - the same will be true with SQLite  
(minimizing the amount of time a write lock is maintained on the  
database file)

> 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.

I will run more detailed numbers on this later, but a quick 1-off on  
changing it to BEING IMMEDIATE still indicates a measurable slowdown,  
between 1% and 2%

> For the current spec, concurrency isn't a huge issue, because
> everything will be serialized at some level anyhow.

Nothing in the current spec forces 2 different browsing contexts from  
operating concurrently, resulting in the possibility of their own  
transactions stomping each other.

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

My concerns about them are more than just performance related ones.  A  
forced performance penalty just drives me mad ;)

~Brady