I see from the plan that it isn't using anything, but it does have a FK
pointing to the table it is joined to. CLAIMAUDIT also happens to be
empty at this point, so that's probably why the optimizer didn't use it.


-----Original Message-----
From: Arno Brinkman [mailto:firebird@...]
Sent: Tuesday, March 29, 2005 6:09 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Extremely poor query performance


Hi,

> 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?

This will avoid using the index on GRANTEE_INVOICE (adding zero or
concatenating empty string if it's a character field)

WHERE
ccf.GRANTEE_INVOICE + 0 IS NULL AND
ccf.POSTED >= ? AND
ccf.POSTED <= ?

Beside that also take attention to your LEFT JOIN, because the table
"CLAIMAUDIT ca" doesn't use any indexes.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features
:
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.8.4 - Release Date: 27-3-2005




Yahoo! Groups Links