I think the conversation in this thread has gotten a little muddled.
The two main issues I see being discussed are:
1. Should single SQL statements be wrapped in a transaction?
2. Should the SQL API support explicit transactions (and therefore a
way to not fall into implicit transactions)?
It sounds like Brady is mostly concerned about (1), while Scott is
mostly concerned about (2). I think it would be helpful to discuss
these as separate issues.
-Adam
On Oct 17, 2007, at 11:24 AM, Brady Eidson wrote:
On Oct 17, 2007, at 11:04 AM, Scott Hess wrote:
On 10/17/07, Brady Eidson <[EMAIL PROTECTED]> 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
