At 09:50 19.02.2003 +1000, Mark Patterson wrote:

>The guy maintaining the code originally did it in paradox and found that
>adding
>indexes led to the import from text file into the database running slower.
>I've been
>saying that the same shouldn't apply to FB.

Paradox is very different from Fb in how you do things, but you do know
that already, don't you? I think indexes can affect speed during imports in
Fb too, but I'm uncertain as to what extent.

>Here's a listing of (the essentials of) the table with its indexes:
>
>Table SAWMEMBERS has 32 fields.
> 1* AUTO_NUMBER Integer REQ
> 2: JOB_NUMBER String 8
> 3: TRUSS_NUMBER String 10
> 4: MEMBER_ID String 8
> 5: STATION_ID Smallint
> 6: SAW_NUMBER Smallint
> ...
> 24: LEFT_MITRE_ANGLE Smallint
> 25: RIGHT_MITRE_ANGLE Smallint
> 26: START_TIME DateTime
> 27: END_TIME DateTime
> 28: MEMBER_DURATION Float
> 29: USER_NAME String 40
> 30: MASTERSAW_REJECTED Smallint
> 31: DATE_ARCHIVED DateTime
> 32: STATUS Smallint
>Index 0: Name=RDB$PRIMARY10 Fields=AUTO_NUMBER Options=Primary,Unique
>Index 1: Name=NO_DOUBLE_MITRES2 Fields=NO_DOUBLE_MITRES
>Index 2: Name=STATION_ID_SMIDX Fields=STATION_ID
>Index 3: Name=SM_STATUS Fields=STATUS
>Index 4: Name=SM_JOB_NUMBER Fields=JOB_NUMBER
>Index 5: Name=SM_START_TIME Fields=START_TIME
>
>I haven't looked at the big database, but on a fair sized on that I am
>testing
>on the number of SAWMEMEBRS records is 906,808; WHERE STATUS <= 3 it's 21,005.

I noticed from your reply to Helen that all queries did involve WHERE
STATION_ID = ... If so, you could add the STATION_ID at the front of most
indexes - and then the PK at the end of indexes that are still not very
selective. E.g.

>Index 3: Name=SM_STATUS Fields=STATION_ID, STATUS, AUTO_NUMBER

>Actually, I find that these queries run fast in most cases. It may be
>because we
>has two threads running and there is some waiting or something going on.

Threads? Well, I know the word, but haven't ever tried using any. If that
is the source of your problems, none of my suggestions will help (or if
they do, it is pure fluke).

Set