From: Jamis Buck <jamis_buck@...>
Date: 2005-01-11T01:15:32+09:00
Subject: Re: Tutorial: TDD for sqlite3-ruby

On 00:06 Tue 11 Jan     , Phlip wrote:
> Jamis Buck wrote:
> 
> > This is a very important point, and I'd like to stress it. SQLite3, in
> > particular, behaves _very poorly_ unless you use explicit
> > transactions. In my testing and benchmarking, I've seen inserts take
> > _orders of magnitude_ longer to execute outside of an explicit
> > transaction, mostly due to disk activity (from reading and writing
> > journals, I imagine).
> 
> So if I write a bunch of test cases, and if setup() erased the database and
> built it again (unpopulated), then how much of the performance hit was from
> rebuilding the database (with 4 simple tables), and how much was from the
> lack of transactions?
> 
> My bad for not profiling, and just assuming it was the rebuilding!

Rebuilding is slow. On my machine, it took (including the time to do
disk IO's) about 6.5 seconds to delete and recreate a simple 4-table
database 100 times. 7.5 seconds to do a create table/drop table
combination, instead of just deleting the database file.

In general, rebuilding the database will be one of the most
time-consuming things your app will do, so your optimization (building
the database once) is a good idea.

I just focused on the transaction thing in my reply because that's
what bit me most recently. :)

-- 
Jamis Buck
jamis_buck@byu.edu
http://www.jamisbuck.org/jamis
------------------------------
"I am Victor of Borge. You will be assimil-nine-ed."