Was experimenting with embedded Firebird today with nHibernate. Wanted to see if an embedded database could keep up with a highly proprietary flat file format for data lookups. I wanted to use SQL Everywhere, but we have to support some customers still on Win 98 (yes Win 98, not even 98 SE). My trivial research on the Internet said Firebird was a likely canidate so I thought I'd give it a shot.
I'm an M$ programmer through and through so everything I use has a slick GUI. First big shock with Firebird :). I can hand code DML with the best of them but I can't remember the last time I used anything but Enterprise Manager to create tables and indexes. Worked briefly with FlameRobin but did not devout enough time to properly learn how to use it. My biggest complaint with Firebird is its week support for indexes. You can have indexes with a combined field size of no more than 240 characters. So that means you can't index that varchar(256) description field. This was a big bummer for my testing. I read a few comments saying that Firebird v2 will bump this restriction to 4096 characters but my attempts to get it working with v2 RC4 were unsuccessful.
Firebird supports a LIMIT like functionality via FIRST x [SKIP] y syntax. Unfortunately, that capability had not been implemented in the nHibernate Firebird Dialect. So being the industrious individual I am, I pulled down the source, coded it up, wrote a quick test fixture, and submitted a patch. It might be coming to a release near you!
http://jira.nhibernate.org/browse/NH-698
The end results of my unscientific tests were Firebird just not performing well enough. We have databases with ~400,000 records that our user search on number and description using contains '%{0}%' style syntax. We update the results on a per-keystroke basis, only retrieving 25 records at a time, I was looking at delays of 2-10 seconds between keystroke, definitly not going to make any of our customers happy. Memory consumtion also shot from 30-100 MB. Running the same test against SQL Server 2000 with a properly indexed table yeilded results of ~0.60 - 0.75 seconds. I'll have to revisit Firebird at a later release and see how it has improved.
posted @ Wednesday, August 23, 2006 7:26 PM