Benching marking Firebird 1.0 vs. 1.5

Yesterday afternoon at work I was stressed in trying to tune some SQL queries, as clients complained Firebird taking up all the CPU time after our software upgrades. At the end, I figured out the issue lies in how Firebird 1.0 and Firebird 1.5 prepare the plan for the exact same query.

General performance wise, no doubt that Firebird 1.5 wins hands down. It is considerably faster that most of our development boxes have switched to 1.5.0 over the last couple of weeks. On the other hand, all of our deployment/production boxes are still sitting on Firebird 1.0.3, which we scheduled to upgrade in the next release of our software.

In most cases, when development environment differs from deployment environment, problems would find you.

That is what happened to some queries that I recently optimised in our development environment, which run flawlessly in Firebird 1.5. But when the identical query executed on a Firebird 1.0 engine, it is a completely different story. Here is some bench mark I have taken, using the excellent IBOConsole. Also note that it is no way a scientific method of getting bench mark from database engines, and two database engines actually run on different hardware and operating systems. Firebird 1.0 runs on a lightly loaded single user Pentium III 700Mhz with Windows 2000, whereas Firebird 1.5 runs on mildly loaded Pentium III 1Ghz with up to dated Gentoo Linux. But you'll get the idea.

As with the query, it basically joins 4 tables together, ranging 13,000 to 40,000 rows, working through some filter and a sub-select. Pretty trivial. Here's some timing:

Database Query Description Timing
Firebird 1.0 1:45.0071
Firebird 1.0Optimised10:32.0317
Firebird 1.0Using 1.5 plan20:08.0613
Firebird 1.5 0:01.0231
1 The same SQL query has been re-arranged to give the same result but generate a better plan. 2 Use the PLAN keyword substituting query plan generated by Firebird 1.0.

From the benchmark, you can easily see what Firebird 1.5 really excels - query optimisation. It would by default generate a much better query plan, and even the old Firebird can greatly benefit from the new plan generated. With my case, Firebird 1.5 intelligently ignore one index that only has 17 unique values amongst 35,000+ rows (which was added to optimise other queries). Whereas Firebird 1.0 would just use indexes whenever it can, regardless the statistics. Disabling that index would then make Firebird 1.0 to generate the same more optimised plan.

It is great that Firebird supports PLAN keyword for the developers to test out the query with different plans. However, there is no easy way to tell a SELECT statement to ignore one particular index - you need to supply the whole plan if you want to do that!

Conclusion? Just use the latest bleeding edge one...