Embedded database recommendations?

[Ray Cote]

At 9:57 AM -0500 2/21/07, Drew Van Zandt wrote:
>Hey guys...
>   I'm shopping for an open-source database ...  narrowed it down to 
>SQLite or Firebird

A little bit of experience with SQLite and a fair bit of experience 
with Firebird.

Firebird is pretty darn robust and quick.
We have it built into a payment processing application that can run 
30 payments per second around the clock. At that speed, I'm typically 
consuming 1-2% of a dual 3GHz desktop processor.
So far, my experience is running Firebird under Windows in embedded mode.

I've had Firebird up into the 3-4million record mark and it is doing 
well for us.
One thing we needed was for the database to be efficient when 
deleting large blocks of data.
Our application does batch processing as well as real-time 
transactions, so we'll import 200,000 transactions in a batch, 
process them, and then delete them when we're done -- all while 
keeping the real-time transactions running. Firebird was able to do 
this much better than MySQL, which was one of the other databases we 
evaluated at the time.

One thing you do need to be careful of with Firebird; transactions 
are managed as versions and there is the concept of the Oldest Active 
Transaction (OAT). If you do not carefully handle your transactions, 
the OAT does not advance and your database can fill up with the cruft 
of intermediate transactions. Not so much a 'problem' per-se, just 
one of the idiosyncrasies of this particular database.

One limitation of embedded is you can only have one application 
talking to it at a time (by definition). However, that application 
can be nicely multithreaded and you can even kick off hot backups of 
the embedded database.

As stated above, our experience is using Firebird embedded on Windows 
(though we do have one significantly smaller project using Firebird 
on Mac OS X at the moment).

As always, YMMV and running some test scenarios on your database 
choices is the best way to choose.

Hope this helps.
--Ray

-- 

Raymond Cote
Appropriate Solutions, Inc.
PO Box 458 ~ Peterborough, NH 03458-0458
Phone: 603.924.6079 ~ Fax: 603.924.8668
rgacote(at)AppropriateSolutions.com
www.AppropriateSolutions.com