Subject | Re: [firebird-support] Performancelost after Migration to FB 2 |
---|---|
Author | Guido Klapperich |
Post date | 2007-07-17T21:06:29Z |
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
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