sqlite performance [was Re: [Caml-list] [announce] ocaml-sqlite 0.3.5]

[Evan Martin <martine@danga.com>]

On Thu, Apr 15, 2004 at 03:53:11PM +0200, Stefano Zacchiroli wrote:
> On Thu, Apr 15, 2004 at 08:44:38AM -0500, John Goerzen wrote:
> > Stefano, do you plan to package this up for Debian?  I'd definately
> > like to see it there.
> 
> I'm interested in principle to the package, and I've also already did
> the packaging for an older version. But if the performance are still
> those I've seen last week, then the package is completely useless to me
> and I wont be interested in maintaining it.

Hi Stefano,

I'm hardly a SQLite expert, nor is the caml-list really the place for me
to answer, but my first guess is this:

SQLite is file backed.  That means any SQL that modifies the database
will actually write out to the file system.  As I recall, there are ways
to change this behavior throughout SQLite (but I can't remember them
well enough to know whether ocaml-sqlite allows them-- I believe you
just need to execute some SQL code using a special PRAGMA statement?).

But more simply and importantly, SQLite only commits to the file when a
transaction is completed.  (There's an implicit transaction around every
INSERT / UPDATE, etc. if you don't specify a transaction yourself.)
I've seen significant performance increases by wrapping repeated
modifications of large datasets with transactions (by using the SQL
statements "BEGIN;" and "END;"), which allows the disk access to be
batched.  With those in place, my simple programs have seen orders of
magnitude increases in performance.

-- 
Evan Martin
martine@danga.com
http://neugierig.org

-------------------
To unsubscribe, mail caml-list-request@inria.fr Archives: http://caml.inria.fr
Bug reports: http://caml.inria.fr/bin/caml-bugs FAQ: http://caml.inria.fr/FAQ/
Beginner's list: http://groups.yahoo.com/group/ocaml_beginners