Subject Re: [firebird-support] Performancelost after Migration to FB 2
Author Guido Klapperich
I have just changed the outer for-select-statement in the following way:

for select GDTitle,GDEAN,GDPUBLISHER,GDPCID
from GFK_DATA join GFK_PRODUCTAREAS on GDGAID=GAID
where GAIsHardware=:Hardware and GDTitleDistinct=GAID and
((:PCTitleList is NULL) or (:PCTitleList containing
';'||F_LRTrim(GDTitle)||';')) and

((:PCEANList is NULL) or (:PCEANList containing
';'||F_LRTrim(GDEAN)||';')) and

((:PCGTIDList is NULL) or (:PCGTIDList containing
';'||GDGTID||';')) and

((:PublisherList is NULL) or (:PublisherList containing
';'||F_LRTrim(GDPublisher)||';')) and

((:PO500IDList is NULL) or (exists (select PCID from PRODUCTS where
:PO500IDList containing ';'||PCPOID500||';' and PCID=GFK_DATA.GDPCID)))
order by GDTitle collate de_de, GDEAN
into GDTitle, GDEAN, GDPUBLISHER, GDPCID do

I have moved all statements like (:Parameter is NULL) to the first
position in the or-statements. I thought, that the position of these
statements doesn't matter, because the optimizer would check them always
first. Here are the results:

DB15: 12 seconds before and after my change
DB2: 38 seconds before and 12 seconds after my change

That's interesting. It seems, that the FB1.5 optimizer checks the part
(:Parameter is NULL) always first independent of the position in the or
statement. Not the FB2 optimizer, here is the position in the or
statement essential.
Is this a known issue?

Regards

Guido