--- David Johnson wrote:

> There are multiple issues impacting your measures.
>
> Which model of connection was used (classic, superserver, or
> embedded)?
>
> Did your stress tests include a suite of concurrency tests?

I realize that I have included too little information.
I am running in superserver mode; the server is running on a Linux
machine. The code is written in Java and uses the JayBird JDBC.
The client program runs on Windows.

It starts a handful of threads, each of which do the following
concurrently:
- Open a database connection.
- Set autocommit off.
- Prepare a number of PreparedStatements.
- In an endless loop, one of the prepared statements is chosen at
random and executed with random parameters. Each single
operation is commited.

There are two tables in the database:
SQL> show table parent;
ID INTEGER Not Null
NAME CHAR(50) Not Null
NUMMER DOUBLE PRECISION Nullable
TS TIMESTAMP Nullable
BIG BLOB segment 80, subtype UNKNOWN
CHARACTER SET N
ONE Nullable
CONSTRAINT PARENT_PK:
Primary key (ID)

SQL> show table child;
ID INTEGER Not Null
PARENT_ID INTEGER Not Null
NAME VARCHAR(50) Not Null
NUMMER INTEGER Nullable
CONSTRAINT CHILD_FK:
Foreign key (PARENT_ID) References PARENT (ID) On Delete Cascade
CONSTRAINT CHILD_PK:
Primary key (ID)

These are the statements executed (in random order):

SELECT COUNT(*) FROM PARENT WHERE NUMMER < ?
SELECT COUNT(*) FROM CHILD WHERE NUMMER < ?
INSERT INTO PARENT (ID, NAME, NUMMER) VALUES (?, ?, ?)
INSERT INTO CHILD (ID, PARENT_ID, NAME, NUMMER) VALUES (?, ?, ?, ?)
DELETE FROM PARENT WHERE ID = ?
DELETE FROM CHILD WHERE ID = ?
SELECT NAME, NUMMER FROM PARENT WHERE ID = ?
SELECT PARENT_ID, NAME, NUMMER FROM CHILD WHERE ID = ?
UPDATE PARENT SET NAME = ?, NUMMER = ? WHERE ID = ?
UPDATE CHILD SET PARENT_ID = ?, NAME = ?, NUMMER = ? WHERE ID = ?
SELECT COUNT(*) FROM PARENT WHERE ID > ?
SELECT COUNT(*) FROM CHILD WHERE PARENT_ID > ?

The first two I refer to as 'table scan', the last two as
'index scan'.

I did two runs, one where only the insert/update/delete statements
were executed, and one where only select statements were executed.

At the same time, similar tests were run from the same client
machine against other DBMS installed on the same server machine.

Firebird beat almost all others on the first run, but performed
abysmal on the second, both for the 'simple selects' and the scans.
Somehow the discrepancy is so big that I thought I should ask
rather than shrug it off.

> The Firebird wire protocol is known to be excessively chatty.

But that should not make a difference between select and update,
should it?

> One of the impacts of a generational architecture is that inserts
> are cleaner than with locking systems, but selects can be slower
> since it is not possible to do an index-only scan.
> A generational system is preferred for systems that mostly
> insert rows (journaling accounting systems), whereas a locking
> system performs better where you are mostly retrieving data (data
> warehousing).
>
> Hope this helps.

It sure does. Indeed I notice that the time taken for 'index scans'
is comparable to that for 'table scans'.

Do you think that the difference you mention can account for
a factor of 4 for 'simple selects' compared to the worst competitor
and a factor of 6 for 'table scans'?

Yours,
Laurenz Albe