[whatwg] Comments on updated SQL API

[Brady Eidson]

There were a few other replies to my message besides Maciej's that are  
in favor of making transactions part of the API instead something a  
developer should manage in SQL directly.  I can see the arguments and  
support that idea.  I just don't think the current model is  
appropriate.  And here is one of the reasons why...

On Oct 17, 2007, at 12:31 AM, Maciej Stachowiak wrote:

> On Oct 16, 2007, at 11:18 PM, Ian Hickson wrote:
>> What are the disadvantages of implicit transactions?
>>
>> What do other people think?
>
> (2) Performance for single statements. We should gain implementation  
> experience to determine if, in likely implementations, it is a  
> significant performance improvement for single statements to be  
> executed without opening a transaction at all.
>
> (3) Performance in the face of concurrency by not holding locks as  
> long. But again closeTranscation() can cover this case if (2) is not  
> an issue.
>
> So I think we need some performance results for (2) to decide if  
> another method is warranted.

#2 and #3 both fall into a more severe category if the user's data is  
on a slow physical device (network filesystem, for example).

The performance hit from an added small amount of code execution for  
the transaction, or for holding a file lock on a slow filesystem is  
likely to turn from "small" to "quite noticeable"

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.

So at best, the implicit transaction requirement is imposing up to a  
5% performance hit.

In reality, with more complex schemas and entering actual different  
values into the database, it might be even worse.

I imagine that *any* potential back end will have a hard time  
optimizing the "1 statement in a transaction" case to be as fast as  
the statement by itself.

Is this mandatory performance hit acceptable?

~Brady