Firebird 2.5 VS Interbase 9/XE - which performing faster?

Question

We are on a situation where we must choose between thoose 2 databases. We are currently on Firebird, but sometime it lag because of it stacking too much transaction history or something and backup-restore shall be applied in order to make things better.

In my specific case: Database have mostly tables filled with numeric fields. There is mostly inner joins on the queries. Almost the same rate i am inserting, and selecting. ( but on future i am looking about more severe selecting ) There is 3 main tables which having a few bilions of records ( keep growing each second ).

But i would like to see which is the best overall into normal loads like thoose above, and overloads - like selecting and working on the selected fields, preformance overall into event trigering and store procedure execution which i am thinking is sufficient good enought knowlege to choose between them ( more opinions is welcome ) and probably will help other peoples to took the descision.

I am asking

• is it the same with Interbase ?
• Is it worth the effort of jumping toward Interbase ?
• Which performing better overall ?
• Is Interbase having this history issue like Firebird, which keep growing database, and slowing it down ?

P.S.: I will leave this question without check for a solution for now. Maybe there will be someone to comare actually the databases on normal, each-day querys base, and the question and results will be more usable for me and the other peoples falled on such situation.

Answer 1

The problem you describe is usually caused by bad transaction management, or long running transactions. In general you don't need a backup and restore to fix this. A backup should be sufficient (as Firebird does extra clean up and garbage collection during backup).

Firebird (and Interbase) both use Multi Version Concurrency Control, meaning that changes are recorded in a new record version. Old record versions are only cleaned up when there are no transactions open that have an interest in that transaction. Record versions that were created by a rolled back transaction is only cleaned up during a sweep.

Bad transaction management (having long running transactions, or using commit retaining instead of commit), unexpected disconnects, etc can mean that transactions are still open, which means that they will need to be cleaned up by the database (a so called sweep in Firebird). This can slow down your database because it needs to read multiple versions of the same record.

As said, the sweep is performed when doing a backup. So just doing a backup should be sufficient to remove most of the problems.

For more detailed information, look at gfix housekeeping

Answer 2

The obvious and I'm afraid rather tedious answer is that you are going to have to benchmark the two using your own workloads. The chances are that your applications workloads will be different from every other benchmark or application.