On Wed, 14 Jan 2004 02:38:00 +0000 (UTC), David Johnson wrote:

> As an experienced IT person in a large company, I am accustomed to having
> an entire department whose concern is the performance of the corporate
> DBMS. However, I am working on a project of my own that will use
> Interbase/Firebird as the back end persistence layer for an enterprise
> scaled distributed system.
> Current environment: Delphi 6 build 6.163, SQLExpress, Firebird 1.5 RC8
> with default configurations
> ...
>
> Observations:
>
> 1. Completion of the insert performance test for 4,000,000 rows into a
> clean test database required 16:16:12 (roughly 68 rows per second) on an
> 800 MHz Win98 box with 512 MB RAM. Every row insert was committed. This
> is slower than I expected. I initially thought that my disappointment
> was because I am used to my DBMS running on big iron (DB2 on large MVS
> systems), but even with M$ Access I have seen far more complex
> opertations perform row inserts at twice that rate. I am certain that
> performance tuning would improve the performance gap. How mch
> improvement can I expect to see?

I've inserted 2000 records per second on a ca. 800MHz XEON 2CPU Win2k box
with 512MB RAM, and this wasn't all the server had do to (it was also
reading these records from another database). Commits at every 10.000
records, I guess. This was a Delphi program using IBO (no special
optimizations), running on the server. The declared record length was
about 500B, but net length was probably just half that.

> 2. Even though every row insert was committed, when the test case was
> completed the DBMS maintains that the table is still "in use" as long as
> the connection is held. Is this a "bug" or a side effect of the
> optimization? I am curious.

Works as designed, I think.

>
> 3. I am accustomed to a "select count (*) from table" query taking
> virtually no time, regardless of the table size. Firebird appears to do
> a table space scan when you run this query. Is this a configurable
> option in Firebird?

Don't do "select count(*) ..." with no where clause. Helen has given more
info on this.

> 4. I applied an index to the create stamp time field so I could readily
> identiy the first and last records from the test run. After indexing,
> the first record was found rapidly, but the last record took several
> minutes to identify. With 4,000,000 records in a B+ tree on 8k Page
> boundaries, I believe that I should have seen a maximum of 40
> milliseconds (3 pages I/O on the index, then 1 page I/O on the table
> space). Does anyone haveany comments or suggestions?

As Helen explained, doing "last" is not a good thing (without an descending
index).

"select count(*) ..." and going to "last" (as if one used a table) can
usually be avoided.

> 5. What sort of performance improvement can I expect to see if I switch
> to embedded SQL? I am targeting this system to support 8,000 concurrent
> users in an active environment. Much of the framework acts to hide the
> DBMS performance limits, but there are limits to how much it can hide.
> :o)

As Helen (again) said, probably not much to gain.
Using prepared (parameterized) queries makes for increased speed.


--
Aage J.