Hi Mike!

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

I've actually never seen this before, when using the inner JOIN the optimizer voluntarily puts Z last in the query and when you force it to put Z last in the query (the LEFT [OUTER] JOIN) it changes the entire plan. I would never have guessed that!

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

With the plans above, I'd recommend you to try the other way around:

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

This should at least hint the optimizer that A is not the best candidate for being first in your PLAN. Hopefully it will be enough for it to choose a good plan, it will definitely be a different plan (though it still has the possibility to choose
PLAN SORT (JOIN (JOIN (A NATURAL,PA NATURAL,D INDEX (PK_DEBTOR)),Z INDEX (PK_ZIP_CODE))) if it really wants to make things slow).

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of SoftTech
Sent: 15. mai 2010 14:06
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] LEFT OUTER JOIN speed on a simple query (Svein)

Greetings Svein,


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]



------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links