[Caml-list] [announce] ocaml-sqlite 0.3.5

[Caml-list] [announce] ocaml-sqlite 0.3.5

[Evan Martin]

Hi everyone,

I fixed up Mikhail Fedotov's nice SQLite[1] bindings for OCaml, and
after I sent him the patch he offered to let me maintain it.  So I have
integrated my patch (and a few other cleanups) and released a new version.

You can get it at
  http://neugierig.org/software/ocaml/sqlite/

Additionally, I have sent a patch to the ocamldbi developers, and
so hopefully SQLite support will be in the next release of ocamldbi.

[1] SQLite is a SQL engine available as a library.  It's good when you
need to use a database but don't want to require a MySQL (, etc.)
install.  See http://www.sqlite.org for details.

-- 
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

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

[Stefano Zacchiroli]

On Wed, Apr 14, 2004 at 03:16:12PM -0700, Evan Martin wrote:
> [1] SQLite is a SQL engine available as a library.  It's good when you
> need to use a database but don't want to require a MySQL (, etc.)
> install.  See http://www.sqlite.org for details.

BTW I've tried ocaml sqlite bindings a while ago and compared their
performance to mysql (using ocaml bindings too). According to sqlite web
page sqlite should be faster than mysql, but in my experience I've got
very different results.

Have you performed any benchmark of sqlite?

Cheers.

-- 
Stefano Zacchiroli -*- Computer Science PhD student @ Uny Bologna, Italy
zack@{cs.unibo.it,debian.org,bononia.it} -%- http://www.bononia.it/zack/
If there's any real truth it's that the entire multidimensional infinity
of the Universe is almost certainly being run by a bunch of maniacs. -!-

-------------------
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

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

[John Goerzen]

On Thu, Apr 15, 2004 at 11:51:04AM +0200, Stefano Zacchiroli wrote:
> On Wed, Apr 14, 2004 at 03:16:12PM -0700, Evan Martin wrote:
> > [1] SQLite is a SQL engine available as a library.  It's good when you
> > need to use a database but don't want to require a MySQL (, etc.)
> > install.  See http://www.sqlite.org for details.
> 
> BTW I've tried ocaml sqlite bindings a while ago and compared their
> performance to mysql (using ocaml bindings too). According to sqlite web
> page sqlite should be faster than mysql, but in my experience I've got
> very different results.

Stefano, do you plan to package this up for Debian?  I'd definately like
to see it there.

FWIW, it would also be excellent if OcamlDBI supported ocaml-sqlite.

-- John

-------------------
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

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

[Stefano Zacchiroli]

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.

Still, I can give you the debian package I did for the older version, it
will probably be easy to forward port it.

Cheers.

-- 
Stefano Zacchiroli -*- Computer Science PhD student @ Uny Bologna, Italy
zack@{cs.unibo.it,debian.org,bononia.it} -%- http://www.bononia.it/zack/
If there's any real truth it's that the entire multidimensional infinity
of the Universe is almost certainly being run by a bunch of maniacs. -!-

-------------------
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

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

[Richard Jones]

On Thu, Apr 15, 2004 at 08:44:38AM -0500, John Goerzen wrote:
> On Thu, Apr 15, 2004 at 11:51:04AM +0200, Stefano Zacchiroli wrote:
> > On Wed, Apr 14, 2004 at 03:16:12PM -0700, Evan Martin wrote:
> > > [1] SQLite is a SQL engine available as a library.  It's good when you
> > > need to use a database but don't want to require a MySQL (, etc.)
> > > install.  See http://www.sqlite.org for details.
> > 
> > BTW I've tried ocaml sqlite bindings a while ago and compared their
> > performance to mysql (using ocaml bindings too). According to sqlite web
> > page sqlite should be faster than mysql, but in my experience I've got
> > very different results.
> 
> Stefano, do you plan to package this up for Debian?  I'd definately like
> to see it there.
> 
> FWIW, it would also be excellent if OcamlDBI supported ocaml-sqlite.

The CVS version of OcamlDBI does have support.

Rich.

-- 
Richard Jones. http://www.annexia.org/ http://www.j-london.com/
Merjis Ltd. http://www.merjis.com/ - improving website return on investment
http://www.winwinsales.co.uk/ - CRM improvement consultancy

-------------------
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

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

[John Goerzen]

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.
> 
> Still, I can give you the debian package I did for the older version, it
> will probably be easy to forward port it.

Please do.  I'll file an ITP now, and then once I get that from you,
I'll update and upload it.  I can then update OcamlDBI and turn on the
sqlite driver in it.  <burns> excellent </burns> :-)

-- John

-------------------
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

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

[Evan Martin]

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

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

[Stefano Zacchiroli]

On Fri, Apr 16, 2004 at 08:00:14PM -0700, Evan Martin wrote:
> 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

I've read this FAQ before on the sqlite site, but it doesn't apply to my
case: I've a database which is static, it is built from scratch from
time to time and I simply need to perform a lot of SELECT on the db.

Cheers.

-- 
Stefano Zacchiroli -*- Computer Science PhD student @ Uny Bologna, Italy
zack@{cs.unibo.it,debian.org,bononia.it} -%- http://www.bononia.it/zack/
If there's any real truth it's that the entire multidimensional infinity
of the Universe is almost certainly being run by a bunch of maniacs. -!-

-------------------
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

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

[Mikhail Fedotov]

Hi,

>> 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

>I've read this FAQ before on the sqlite site, but it doesn't apply to my
>case: I've a database which is static, it is built from scratch from time
>to time and I simply need to perform a lot of SELECT on the db.

A trivial suggestion, but this usually comes from absence of
indexes or sql queries which fail to use existing indexes. I.e.
you have a good reason to check how your sql queries are
compiled into sqlite virtual machine instructions.

This was discussed in the sqlite list from time to time.

Mikhail


-------------------
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

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

[Stefano Zacchiroli]

On Mon, Apr 19, 2004 at 07:16:54PM +0400, Mikhail Fedotov wrote:
> A trivial suggestion, but this usually comes from absence of
> indexes or sql queries which fail to use existing indexes. I.e.

I've indexes and I'm almost sure they were used since performances with
or without them are significantly different. Anyway this discussion is
starting to be too much OT for this list. I will move it to the sqlite
ML.

Sorry for the noise.
Cheers.

-- 
Stefano Zacchiroli -*- Computer Science PhD student @ Uny Bologna, Italy
zack@{cs.unibo.it,debian.org,bononia.it} -%- http://www.bononia.it/zack/
If there's any real truth it's that the entire multidimensional infinity
of the Universe is almost certainly being run by a bunch of maniacs. -!-

-------------------
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