From mboxrd@z Thu Jan 1 00:00:00 1970 Received: (from majordomo@localhost) by pauillac.inria.fr (8.7.6/8.7.3) id FAA00496; Sat, 17 Apr 2004 05:00:31 +0200 (MET DST) X-Authentication-Warning: pauillac.inria.fr: majordomo set sender to owner-caml-list@pauillac.inria.fr using -f Received: from concorde.inria.fr (concorde.inria.fr [192.93.2.39]) by pauillac.inria.fr (8.7.6/8.7.3) with ESMTP id FAA04959 for ; Sat, 17 Apr 2004 05:00:30 +0200 (MET DST) Received: from mxout4.cac.washington.edu (mxout4.cac.washington.edu [140.142.33.19]) by concorde.inria.fr (8.12.10/8.12.10) with ESMTP id i3H30SYM003622 for ; Sat, 17 Apr 2004 05:00:29 +0200 Received: from mailhost2.u.washington.edu (mailhost2.u.washington.edu [140.142.33.2]) by mxout4.cac.washington.edu (8.12.11+UW04.02/8.12.11+UW04.03) with ESMTP id i3H30JSB009724; Fri, 16 Apr 2004 20:00:23 -0700 Received: from lulu (D-128-208-200-44.dhcp4.washington.edu [128.208.200.44]) by mailhost2.u.washington.edu (8.12.11+UW04.02/8.12.11+UW04.03) with ESMTP id i3H30Jc9025434; Fri, 16 Apr 2004 20:00:19 -0700 Received: from martine by lulu with local (masqmail 0.2.20) id 1BEg3y-3gK-00; Fri, 16 Apr 2004 20:00:14 -0700 Date: Fri, 16 Apr 2004 20:00:14 -0700 From: Evan Martin To: Stefano Zacchiroli Cc: caml-list@inria.fr Subject: sqlite performance [was Re: [Caml-list] [announce] ocaml-sqlite 0.3.5] Message-ID: <20040417030014.GA14011@lulu> References: <20040414221612.GA21960@danga.com> <20040415095104.GA5908@fistandantilus.takhisis.org> <20040415134438.GB28237@excelhustler.com> <20040415135311.GA1441@fistandantilus.takhisis.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20040415135311.GA1441@fistandantilus.takhisis.org> User-Agent: Mutt/1.5.5.1+cvs20040105i X-Miltered: at concorde by Joe's j-chkmail ("http://j-chkmail.ensmp.fr")! X-Loop: caml-list@inria.fr X-Spam: no; 0.00; evan:01 caml-list:01 2004:99 2004:99 caml-list:01 modifies:01 datasets:01 evan:01 0200,:01 orders:97 implicit:02 wrote:03 wrote:03 zacchiroli:03 behavior:03 Sender: owner-caml-list@pauillac.inria.fr Precedence: bulk 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