Five Years with Firebird

By August 8, 2009Database

It’s unbelievable, but the Interbase with VB article has been written over five years ago. Over the years the article has received hundreds of thousands of hits and I hope we have left a mark with the developer community. This little write-up is intended as a follow-up or more like a status update on our experience with Interbase/Firebird.

Even though the original article is fairly old by internet/development standards , most of the facts still stand. However, life never stands still, especially not for small agile software companies. By the end of 2001, Borland has seized any future development on the open-sourced version of Interbase. Fortunately a group of amazing developers has picked up the slack and forked the code base and in winter 2002 we have switched to Firebird 1.0 the “true” open source Interbase and the following year to Firebird 1.5.With close to 1000 deployments on various desktop and server configurations and thousands of gigabytes of data spread across all the databases, I think we can consider ourselves as seasoned Firebird users/developers.

But, as with any technology, we had our share of challenge and success stories. Following are a few items what to look for and what to look out for. Let’s start with the issues as these are most of the time the determining factor when it comes to adoption of new technology:

The Issues

Firebird is not very forgiving when it comes to sloppy SQL syntax. Things that you can get away with in SQL Server, will kill the db performance in Firebird. For instance:

Select * from SomeTable where id in (Select some_id from SomeOtherTable)

If the subselect should return a lot of records, the performance will be abysmal. Fortunately if you think outside of the box, there is always a workaround. In this case we could use a selectable stored proc to do an inner join on like this:

Select * from SomeTable inner join SelectableStoredProcedure(Params) on (someid = stored_proc_param)

Another issue that we have run into a few times is the high bandwidth requirement when transferring large blob datasets over the Ethernet. Essentially , when performing a query that returns a lot of data (lets say 100 MB) to the client, Firebird will max out the Ethernet bandwidth on a 100 Mbps switch very quickly without even making the hard drives work. SQL Server performs much better in this instance.

One feature that we sorely miss is the cross database joins that are easily done in SQL server. There are workarounds in Firebird (like defining an external table) but it’s not the same. Hopefully, this feature will be added to FB 2.0.

The Awesome

Enough of the issues, lets cover the exciting items, things that have made our lives easy:

The deployment as part of a custom setup is a breeze. With all our deployments we have had only 3 problem installations. One was caused by a faulty Ethernet card and the other two on Windows ME. I think this metric speaks for itself. In contrast, we are supporting an outside company with MSDE deployments. The failure rate is an astonishing 25% for MSDE deployments in uncontrolled environments. Most of the time these are issues that can be resolved, but the cost of support is very high.

Mentioned before, selectable stored procedures. The power of this feature becomes evident when you can do an inner or outer join between a table and a stored procedure. In addition, stored procedures work “internally” similar to cursors (there is a loop inside the stored proc) where additional programming logic can be embedded.

Firebird also works well in many environments. Currently we have a .NET project with Firebird deployment on Win2K3, VB6 projects and even Firebird running with Ruby on Rails on a Linux host.

Hopefully this little status update will be useful to somebody. What’s important that after five years, we are still sticking with Firebird and there is no change in sight…

Leave a Reply