Firebird query performance with text search, also some numbers

firebirdperformancequery-performance

I have situation as follows: I'm having a table OFERTY of 10k+ records, and when I select * from it, I get

PLAN (OFERTY NATURAL)
Executing...
Done.
656 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 0 index, 312 seq.
Delta memory: -95312 bytes.
Total execution time: 0.188s
Script execution finished.

This is all fine, but a problem manifests when I need to present data from many joined tables on a listview, and allow filtering on text fields using starting with clause. I get these numbers:

PLAN JOIN (JOIN (OFERTY NATURAL, KL INDEX (KONTRAHENT_PK), PL INDEX (KONTRAHENT_PK),
PERSONEL INDEX (RDB$PRIMARY28), STATUSY INDEX (RDB$PRIMARY34), KOLORYSTYKA INDEX
(KOLORYSTYKA_PK), MODELE INDEX (MODELE_PK), A INDEX (IDX_SLOWNIKI_NUM_DZ), B INDEX
(IDX_SLOWNIKI_NUM_DZ), C INDEX (IDX_SLOWNIKI_NUM_DZ), RKOL INDEX (REJESTR_PK), RSZY
INDEX (REJESTR_PK)), KLIENCI_PROJEKTANCI INDEX (RDB$PRIMARY53))
Executing...
Done.
67370 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 11199 index, 10669 seq.
Delta memory: -20 bytes.
Total execution time: 2.610s
Script execution finished.

As you can see, query time is skyrocketing. I think it reads ALL the records instead of first-some and I don't know why. There are indexes everywhere there's a need for them (as far as I know). The very same query, but without following text search clause:

/*kl.kt_nazwa is from table KONTRAHENCI which has alias KL and is joined to OFERTY*/
and kl.kt_nazwa starting with 'strefa'

has exactly the same plan, but performs as follows:

Executing...
Done.
10801 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 2444 index, 200 seq.
Delta memory: -1036 bytes.
Total execution time: 0.203s
Script execution finished.

That's a HUGE difference. It doesn't need to read ALL the records from table KONTRAHENCI. So I have two questions here:

  • Is there a way of text search in firebird that does not mandate reading ALL records from table (in this case: KONTRAHENCI)?
  • there's also awful lot of fetches. Please explain what they are?

Best Answer

Nothing suspicious in the plan itself, though I do wonder what your actual query looks like. If KONTRAHENCI.kt_nazwa has an index and there also is an index for the field in OFERTY that links to the primary key field of KONTRAHENCI, then a better plan might be something like: PLAN JOIN (JOIN (KL INDEX (KT_NAZWA_INDEX), OFERTY INDEX (LINK_FIELD_KONTRAHENCI...

Are you by any chance not using INNER JOINs, but LEFT or RIGHT [OUTER] JOINs? That would prevent Firebird from choosing the optimal index.

Related Question