1

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?
1

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.