Greetings Svein,

Plan for LEFT OUTER JOIN is:
PLAN SORT (JOIN (JOIN (A NATURAL,PA INDEX (REFADDRESS1),D INDEX (PK_DEBTOR)),Z INDEX (PK_ZIP_CODE)))

Plan for INNER JOIN is:
PLAN SORT (JOIN (D INDEX (REFACCOUNT131),PA INDEX (REFPERSON2),A INDEX (PK_ADDRESS),Z INDEX (PK_ZIP_CODE)))

Trying this had no effect:
JOIN ADDRESS A ON A.ADDR_ID+0 = PA.ADDR_ID

DEBTOR PK = ACCT_ID, PERSON_ID 208972 Records
No other indicies

PER_ADDRESS PK = PERSON_ID, ADDR_ID 353953 Records
No other indicies

ADDRESS PK = ADDR_ID 274760 Records
IX_ADDRESS_ADDRESS1 = ADDRESS1
IX_ADDRESS_ADDRESS2 = ADDRESS2

ZIP_CODE PK = ZIP_CODE_ID 50608 Records
No other indicies

You asked "what is the selectivity of all indexed fields involved in the JOIN and WHERE clauses etc."
I use Database Workbench as my Firebird development tool. How do I find this information?

I appreciate your help.
Thanks,
Mike

----- Original Message -----
From: Svein Erling Tysvær
To: firebird-support@yahoogroups.com
Sent: Friday, May 14, 2010 2:39 AM
Subject: {Disarmed} RE: [firebird-support] LEFT OUTER JOIN speed on a simple query



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



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

[Non-text portions of this message have been removed]