At 05:23 PM 1/08/2006, you wrote:

>Hi all...
>
>I'm running a small application against a small FB 2.0 RC 3 database
>
>The main functions used by my application are giving me the following
>results
>
>Function 1 - 0.001956ms - Avg time per execute. The function does 1 to
>3 SELECTS against a table containing around approx 29500 records.
>Selection is done on 1 column
>
>Function 2 - 0.623543ms - Avg time per execute. The function does 1 to
>3 SELECTS against a table containing around approx 29500 records.
>Selection is done on 1 column
>
>Function 3 - 0.750933ms - Avg time per execute. The function does 1 to
>3 SELECTS against a table containing around approx 29500 records.
>Selection is done on 1 column
>
>Function 4 - 0.667124ms - Avg time per execute. The function does a
>SINGLE SELECT against a table containing approx 775000 records.
>Selection is done on 1 column
>
>Function 5 - 5.906617ms - Avg time per execute. The function will run
>1 to 3 queries against a table containing approx 29500 records.
>Selection is done on 2 -> 3 columns
>
>At present the total time to process approx 27000 records is around
>320 seconds
>
>All columns used by the select statements of the various functions are
>indexed. I've got no compound indices at the moment. Would they
>improve performance?
>
>The application and database are hosted on a WinXP Pro, AMD 4200 Dual
>Core, with 1GB RAM and 160GB SATA disk. I'm using the embedded client
>libraries to access the DB. I've set the buffer size of the database
>to 50000. Which showed a marked increase in performance.
>
>Do these numbers look fairly decent for a firebird installation. Is
>there any advice with regarding to tweaking the embedded driver setup
>to improve caching / performance? My queries are all very simple
>single table selects. "SELECT * FROM TABLE WHERE X = 'D' AND Y = 'C'
>AND Z = 'E' type stuff.
>
>Any help would be greatly appreciated. If any more info is required
>just shout ;)

How long is a piece of string? You can learn a lot from looking at
the plan to see what indexes are being used.

1. You can make questions like this more useful by supplying an
actual query that seems slow, along with the plan and the index
statistics (the relevant output from gstat -i). Then Set will put on
his "beat-the-optimizer" hat and give you some tricks to try.

2. Indexes with low selectivity will slow down queries rather than
speed them up. If your search indexes have a lot of duplicates of a
small catchment of possible values, you should be looking at tricks
to prevent them from being used.

./heLen