Thanks for your excellent feedback Helen, Aage, Nigel, and Alan.

Since I need to preserve upgrade pathways, I am limiting myself to SQL constructs that are supported by all of the DBMS I hope to target. The fewer change I need to make to the product, the happier I will be. The "FIRST" predicate is useful, but it is not available in DB2 (my preferred upgrade path). Likewise, the EXISTS predicate as applied to tables is not available in DB2, so alternate mechanisms need to be found for determining table existence. Since the tables in my framework share certain characteristics that appear in the primary key, I can determine table existence by "select the min(OID) from table". This will work quickly in all DBMS.

I have added your data to my chart (hopefully sent below). I have extrapolated that your test required roughly 50 minutes to compete, based on the timing figures you published. Note that the chart is incomplete - the full chart includes CPU speed and other performance factors.

Overheads related to prepping the data amount to 2 minutes. Overheads related to transferring parameters from the testcase into the DBExpress component (not from DBExpress to Interbase) are estimated to be roughly 60 minutes (half of the measured transaction time - test case is still running). Based on the chart timings, I should see roughly double the performance in this application by dropping down a layer and interfacing to firebird directly. Of course, this means that my argument for avoiding interbase specific SQL goes away too ... but for 50% improvement I can live with that.

I am preparing a random read test case that I will run and post results from later today.

This exercise is beginning to give me a feel for Firebird's structure and design parameters.




Insert
Insert
Insert

Rows
Rows per transaction
Transactions
Time
Rows per second
Trans per second

4000000
1
4000000
58572
68.29
68.29

4000000
10
400000
9782
408.91
40.89

4000000
10000
400
2962.96
1350
0.14





[Non-text portions of this message have been removed]