Subject Re: Firebird performance with much data
Author mgastde
Hi Geert,
--- In ib-support@y..., Geert Bevin <gbevin@u...> wrote:
> Hi,
> I'm in the process of having to recommend a database server for a new
> project. I've been using Firebird until now mainly as an embedded
> database for standalone applications under linux. This project however
> requires a JDBC connection to a standalone database server which will
> contain millions of rows in a simple database structure. The structure
> consist of a few tables with only long integers and varchars. Really
> nothing fancy. The queries are also expected to be very simple with a
> maximum of one join.
We are currently running a production control system with very big
tables. The maximum record count for one sole table is about 10 Mio.
We are accessing this table with complex join operations (a maximum of
8 joins) in transactions that insert / select up to 85000 records (up
to 40000 blob records containing a blob field).

Before we started to implement our system, we compared MS SQL Server
7.0, Oracle 8.1.5 and Interbase 6.0 and Interbase was much faster than
its competitors (up to factor 6, compared with SQL-Server and up to
factor 1.5, compared with Oracle).

The performance is quite fair:
On a 750 MHz machine with 512 MB of RAM running Linux and Firebird in
a multi user environment we reach process up to about 20000 records
per minute including 10000 blob fields.
Accessing small data sets with two or three joins usually takes far
less than a second.
For my opinion, there is a pitfall in Firebird: Due to the
multigeneration architecture, updated, deleted and rolled back record
versions still remain in your database file. The space, they are
consuming, can be freed automatically in later transactions. This
requires, that the oldest transaction number is higher than the
transaction number of the the transaction that performed the update,
delete (or roll back) operation before. Be aware, that you keep the
oldest transaction number and the next transaction number close
together. This is a question of your application design.