Hi, while evaluating the impact of migrating our application from FB 1.5
to FB 2.x I ran into a query difference, which on itself is not so
strange as there are ofcourse differences between the two versions, but
this involves a relatively simple query as far as I can see it. And the
performance impact for our application though, is potentially
substantial.

What did I do:

- I installed the FB versions (all Win32 SS) on the same machine (FB
1.5.5 / 2.0.5 / 2.1.2 and 2.1.3 snapshop)

- restored the same backup file (backup originally made with FB
1.5.5),

- ran the query below twice.

The executed plan in FB 1.5 uses only indexes (primary, foreign and
secondary), but FB 2.x somehow starts out with a natural tablescan
(slowing the query down).



Results:



Firebird 1.5.5 (Win32 - SS):

PLAN JOIN (ORG INDEX (RDB$PRIMARY42),ACT INDEX
(IDX_ACT_ACTDT_GRPDLN),DLN INDEX (RDB$PRIMARY5),ZRG INDEX
(RDB$PRIMARY15),ATP INDEX (RDB$PRIMARY10))

(processed 8.375 sec - rows fetched 293930)



Firebird 2.0.5.13206 (Win32 - SS):

PLAN JOIN (ATP NATURAL, ACT INDEX (RDB$FOREIGN76, IDX_ACT_ACTDT_GRPDLN),
DLN INDEX (RDB$PRIMARY5), ZRG INDEX (RDB$PRIMARY15), ORG INDEX
(RDB$PRIMARY42))

(processed 59.344 sec - rows fetched 293930)



Firebird 2.1.2 (Win32 - SS):

PLAN JOIN (ATP NATURAL, ACT INDEX (RDB$FOREIGN76, IDX_ACT_ACTDT_GRPDLN),
DLN INDEX (RDB$PRIMARY5), ZRG INDEX (RDB$PRIMARY15), ORG INDEX
(RDB$PRIMARY42))

(processed 25.985 sec - rows fetched 293930)



Firebird 2.1.3 (snapshot downloaded 5-6-2009) (Win32 - SS):

PLAN JOIN (ATP NATURAL, ACT INDEX (RDB$FOREIGN76, IDX_ACT_ACTDT_GRPDLN),
DLN INDEX (RDB$PRIMARY5), ZRG INDEX (RDB$PRIMARY15), ORG INDEX
(RDB$PRIMARY42))

(processed 25,765 sec - rows fetched 293930)



select org.naam AS ORGANISATIE,

zrg.client_id AS CLIENT_NR,

act.act_dt AS DATUM,

atp.NAAM AS ACTIVITEIT_NAAM

from organisatie org

inner join zorgtraject zrg on zrg.org_id = org.org_id

inner join grp_deelname dln on dln.zorg_id = zrg.zorg_id

inner join activiteit act on act.grpdln_id = dln.grpdln_id

inner join activiteittype atp on atp.atype_id = act.atype_id

where org.org_id = 1

and (act.act_dt>='2008-01-01' AND

act.act_dt<='2008-12-31')



Table information:

ORGANISATIE 11 rows

ZORGTRAJECT 24.570 rows

GRP_DEELNAME 75.632 rows

ACTIVITEIT 1.123.475 rows

ACTIVITEITTYPE 1.423 rows

All primary keys are of type 'Integer'.



The foreign key RDB$FOREIGN76 refers to the link to ACTIVITEITTYPE
(ATYPE_ID)

The secondary index used in all cases is:

CREATE ASC INDEX IDX_ACT_ACTDT_GRPDLN ON ACTIVITEIT (ACT_DT, GRPDLN_ID);



Database properties (if interested):

Page Size 8192

Page buffers 4096

Forced Writes ON

Sweep Interval 0





Is there a logical explanation for this behaviour?





Side note: after creating the database from scratch (so there are no
records in it), FB 2.0.5 does adopt another PLAN:

PLAN JOIN (DLN NATURAL, ZRG INDEX (PK_ZORGTRAJECT), ORG INDEX
(PK_ORGANISATIE), ACT INDEX (RDB$FOREIGN52, IDX_ACT_ACTDT_GRPDLN), ATP
INDEX (PK_ACTIVITEITTYPE))

(not tried with other versions though)



Kind regards,

Michiel



[Non-text portions of this message have been removed]