--- In firebird-support@yahoogroups.com, "neal.criscuolo"
<neal.criscuolo@...> wrote:

>
> I am currently evaluating the process of upgrading from Firebird

1.54

> to 2.03 and have hit a problem. Having saved a 1.54 database and
> restored it to 2.03 everything so far seems to work well except the
> following query:
>
> select J.JOB_ID,
> J.JOB_NUMBER,
> C.SHORT_NAME as CUSTOMER_NAME,
> B.BRANCH_NAME,
> S.DESCRIPTION as JOB_STATUS,
> J.CUST_REF_NO
> from JOB J,
> CUSTOMER C,
> BRANCH B,
> JOBSTATUS S
> where J.JOB_ID LIKE '544%'
> and C.CUSTOMER_ID = J.CUSTOMER_ID
> and B.BRANCH_ID = J.BRANCH_ID
> and S.JOB_STATUS_CODE = J.JOB_STATUS_CODE
> order by J.JOB_ID DESCENDING
>
> JOB table has the following index:
> CREATE DESCENDING INDEX IX_JOB_ID_DESC ON JOB(JOB_ID);
>
> When executing this query, Firebird 1.54 uses the following plan:
> PLAN JOIN (J ORDER IX_JOB_ID_DESC,C INDEX (RDB$PRIMARY66),B INDEX
> (RDB$PRIMARY73),S INDEX (RDB$PRIMARY62))
> while Firebird 2.03 uses a different plan, as follows:
> PLAN SORT (JOIN (C NATURAL, J INDEX (RDB$FOREIGN106), S INDEX
> (RDB$PRIMARY62), B INDEX (RDB$PRIMARY73)))
> The result is that this query runs very slow on the Firebird 2.03
> database because the IX_JOB_ID_DESC index was not utilised in the
> plan.
>
> Can anyone shed any light on why this might be happening?
>
> Thanks
> Neal Criscuolo
>

Thank you for your replies, much appreciated. Woody's example code
did work perfectly utilising index IX_JOB_ID_DESC but yes I take your
point Helen that there are a few issues here that need to be
addressed with this "ancient" SQL code.

Thanks again
Neal