At 08:24 AM 27/02/2008, you wrote:

>The following query appears to put Firebird 1.5.3 into a loop.
>Processor goes to 100% and the query never appears to finish.
>
>SELECT A.LD_LOCATION, A.LD_LOAD_NUMBER, A.LD_LOAD_SUFFIX, A.LD_LH_MILES,
> E.LD_LOCATION AS LOAD_LOC, E.LD_LOAD_NUMBER AS LOAD_NUM,
> E.LD_LOAD_SUFFIX AS LOAD_SFX, E.LD_NUM_STOPS, E.LD_TTL_REVENUE,
> E.LD_TTL_ASS_REVENUE, E.LD_TTL_CART_AMOUNT
> FROM LOADS A
> INNER JOIN LD_STOP B ON B.LDS_TEMPLATE_FLAG = 'N' AND
> B.LDS_LOCATION = A.LD_LOCATION AND
> B.LDS_LOAD_NUMBER = A.LD_LOAD_NUMBER AND
> B.LDS_LOAD_SFX = A.LD_LOAD_SUFFIX
> INNER JOIN MF_STOP_STOPS C ON C.MFSS_STOP_ID = B.LDS_STOP_ID
> INNER JOIN LD_STOP D ON D.LDS_STOP_ID = C.MFSS_LDS_STOP_ID
> INNER JOIN LOADS E ON E.LD_TEMPLATE_FLAG = 'N' AND
> E.LD_LOCATION = D.LDS_LOCATION AND
> E.LD_LOAD_NUMBER = D.LDS_LOAD_NUMBER AND
> E.LD_LOAD_SUFFIX = D.LDS_LOAD_SFX
> WHERE A.LD_TEMPLATE_FLAG = 'N' AND
> A.LD_MANIFEST_FLAG <> ' '
> ORDER BY A.LD_TEMPLATE_FLAG, A.LD_LOCATION, A.LD_LOAD_NUMBER,
>A.LD_LOAD_SUFFIX
>
>The plan shows that indices are being used for all JOINS:
>
>PLAN SORT (JOIN (B INDEX (LDS_LOAD_KEY),A INDEX (LD_LOAD_NUMBER_KEY),E
>INDEX (LD_LOAD_NUMBER_KEY),C INDEX (RDB$PRIMARY87),D INDEX
>(RDB$PRIMARY39)))
>
>The same query with the last JOIN (LOADS E) omitted completes in less
>than a minute. I have let the full query run for 15 minutes, with
>Firebird at 100% CPU the whole time, before I finally killed it.
>
>This is the query that works without problems. As I said before it
>runs in less that 1 minute with Firebird at 5% - 10% of CPU.
>
>SELECT A.LD_LOCATION, A.LD_LOAD_NUMBER, A.LD_LOAD_SUFFIX, A.LD_LH_MILES,
> D.LDS_LOCATION AS LOAD_LOC, D.LDS_LOAD_NUMBER AS LOAD_NUM,
> D.LDS_LOAD_SFX AS LOAD_SFX
> FROM LOADS A
> INNER JOIN LD_STOP B ON B.LDS_TEMPLATE_FLAG = 'N' AND
> B.LDS_LOCATION = A.LD_LOCATION AND
> B.LDS_LOAD_NUMBER = A.LD_LOAD_NUMBER AND
> B.LDS_LOAD_SFX = A.LD_LOAD_SUFFIX
> INNER JOIN MF_STOP_STOPS C ON C.MFSS_STOP_ID = B.LDS_STOP_ID
> INNER JOIN LD_STOP D ON D.LDS_STOP_ID = C.MFSS_LDS_STOP_ID
> WHERE A.LD_TEMPLATE_FLAG = 'N' AND
> A.LD_MANIFEST_FLAG <> ' '
> ORDER BY A.LD_TEMPLATE_FLAG, A.LD_LOCATION, A.LD_LOAD_NUMBER,
>A.LD_LOAD_SUFFIX
>
>Is this a bug or is there some way to restructure the query to make it
>run faster.

The first thing I would want to do here is move the search criterion out of the JOIN clause:

INNER JOIN LOADS E ON
/* E.LD_TEMPLATE_FLAG = 'N' AND */
E.LD_LOCATION = D.LDS_LOCATION AND
E.LD_LOAD_NUMBER = D.LDS_LOAD_NUMBER AND
E.LD_LOAD_SUFFIX = D.LDS_LOAD_SFX
WHERE A.LD_TEMPLATE_FLAG = 'N' AND
A.LD_MANIFEST_FLAG <> ' '
/* ---------------------------------------------------- */
and E.LD_TEMPLATE_FLAG = 'N'
/* ---------------------------------------------------- */
ORDER BY A.LD_TEMPLATE_FLAG, A.LD_LOCATION, A.LD_LOAD_NUMBER,
A.LD_LOAD_SUFFIX

./heLen