Subject SELECT ONLY database query performance
Author thefourie2000
Hi all...

The basics of my application are as follows.

- I'm using FB Embedded 2.0
- A TCP Server receives a connection, creates a new thread and
to the DB via the Embedded Engine
- Each thread makes it's own connection to the database.
- The system validates information, starting a transaction, executing
10 -> 15 selects and commiting the transaction. As stated the
system executes ONLY SELECTS
- All queries run against a single table with approx 35k rows.
- Five columns in the table are used to form the WHERE clause and
indexes have been created on those fields as required by the
- The result is then transmitted back to the client via TCP

When processing requests for a SINGLE thread the AVG time per request
is 7ms. If I increase the number of requesting applications and thus
threads connected to the database to 200. The AVG time per request
goes to 1400ms. So it appears that somewhere something is locking the
entire database and not allowing other threads read access. :(

I've tried fiddling with some of the Firebird Config settings, BUT
that has mostly lead to poorer performance.

Is the locking occuring in the FB Embedded DLL? Is there anyway to
disable this locking? I have no need for it since the DB will NEVER
be written to by the application. I'm even wiling to roll my own
embedded DLL.

Any help is welcome as well as any questions regarding the problem