Please show us the chosen PLANs for both the LEFT [OUTER] JOIN option and the [INNER] JOIN. My guess is that things would speed up if you made this minor change:

JOIN ADDRESS A ON A.ADDR_ID+0 = PA.ADDR_ID

but with no knowledge about neither the plans nor your indexes or table contents (how many records in each table, what is the selectivity of all indexed fields involved in the JOIN and WHERE clauses etc.) it is only guessing.

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of SoftTech
Sent: 13. mai 2010 17:27
To: firebird-support@yahoogroups.com
Subject: [firebird-support] LEFT OUTER JOIN speed on a simple query

Greetings All,

The following query takes over 9 seconds:

SELECT DISTINCT CAST(0 AS SMALLINT) AS SELECTED,
A.ADDR_ID,
A.ADDRESS1,
A.ADDRESS2,
Z.CITY,
Z.COUNTY_NAME AS COUNTY,
Z.STATE_CODE AS STATE,
A.ZIP_CODE_ID,
Z.ZIP_CODE,
A.ZIP_CODE_SUFFIX,
A.DEFAULT_ROS_TYPE,
A.NOTE,
CAST (0 AS SMALLINT) AS USPS_STANDARDIZED_IMAGE_INDEX,
A.USPS_STANDARDIZED_DATE,
A.USPS_STANDARDIZED_USER
FROM DEBTOR D
JOIN PER_ADDRESS PA ON PA.PERSON_ID = D.PERSON_ID
JOIN ADDRESS A ON A.ADDR_ID = PA.ADDR_ID
LEFT OUTER JOIN ZIP_CODE Z ON Z.ZIP_CODE_ID = A.ZIP_CODE_ID
WHERE D.ACCT_ID = 36813
AND PA.ADDR_ID <> 48399

Simply changing the LEFT OUTER JOIN to a JOIN is instant. The issue is
there are 203 addresses in the ADDRESS database that do not have the
ZIP_CODE_ID set.

Any ideas on how to speed this query up when using the LEFT OUTER JOIN?

Thanks,
Mike