To answer my own question, I changed the where clause to
WHERE ccf.GRANTEE_INVOICE + 1 IS NULL to keep the optimizer from using
that key. It now takes less than two seconds.
Once we've finished the first run of invoices I should be able to put it
back to how it was.

-----Original Message-----
From: Rick Debay [mailto:rdebay@...]
Sent: Tuesday, March 29, 2005 5:39 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Extremely poor query performance


SELECT
* // columns do not affect the speed
FROM
CLAIMSCALCFEES ccf
JOIN CLAIMSPAIDREVERSED cpr
ON ccf.RXCLAIMNBR = cpr.RXCLAIMNBR AND ccf.RXCLAIMSEQ =
cpr.CLMSEQNBR AND ccf.CLAIMSTS = cpr.CLAIMSTS
JOIN CHC_PHARMACY cp
ON cp.PHARMACY = cpr.SRVPROVID AND cp.CHC = cpr.ACCOUNTID
JOIN CHC c
ON c.CHC = cp.CHC // changing to c.CHC = cpr.ACCOUNTID has no effect
LEFT JOIN CLAIMAUDIT ca
ON ca.RXCLAIMNBR = cpr.RXCLAIMNBR AND ca.RXCLAIMSEQ = cpr.CLMSEQNBR
AND ca.CLAIMSTS = cpr.CLAIMSTS

Here's the plan:
PLAN JOIN (JOIN (CPR NATURAL,CCF INDEX (PK_CLAIMSCALCFEES),CP INDEX
(RDB$PRIMARY1),C INDEX (PK_CHC)),CA NATURAL)

All joins are on foreign keys. The only indexed field is
CLAIMSCALCFEES.POSTED.

This runs in less than a second. Adding either of these two where
clauses, will cause the query to take a few seconds:

WHERE ccf.GRANTEE_INVOICE IS NULL
PLAN JOIN (JOIN (CPR NATURAL,CCF INDEX
(FK_CCF_GRANTEEINVOICE,PK_CLAIMSCALCFEES),CP INDEX (RDB$PRIMARY1),C
INDEX (PK_CHC)),CA NATURAL) WHERE ccf.POSTED >= ? AND ccf.POSTED <= ?
PLAN JOIN (JOIN (CPR NATURAL,CCF INDEX (PK_CLAIMSCALCFEES),CP INDEX
(RDB$PRIMARY1),C INDEX (PK_CHC)),CA NATURAL)

Combining the where clauses results in a query that takes an hour, with
a similar plan:
PLAN JOIN (JOIN (CPR NATURAL,CCF INDEX
(FK_CCF_GRANTEEINVOICE,PK_CLAIMSCALCFEES),CP INDEX (RDB$PRIMARY1),C
INDEX (PK_CHC)),CA NATURAL)

I think the problem is that FK_CCF_GRANTEEINVOICE is not useful here, as
currently ccf.GRANTEE_INVOICE is always null, as nothing has been
invoiced yet using our new system. It would also be nice if it would
use the index on POSTED.
So how do I get the optimizer it to stop using FK_CCF_GRANTEEINVOICE, or
at least to use PK_CLAIMSCALCFEES first?



Yahoo! Groups Links