Svein Erling Tysvær wrote:

>Hi Todd!
>
>The main difference that I see, is that the first query uses one
>(hopefully) selective index for the second table in the plan, whereas
>the second uses one selective index and one hopelessly non-selective
>index (unless you have records spanning many years) for the second
>table. I assume it is the second plan that is slow.
>
>

Yes, the second plan is slow.

>The differences between the two databases may be the data itself?
>

The two databases that I have checked are very similiar. Something like
34,000 records in the Property file and something like 54,000 records in
the sales file.

>There are several things involved in choosing the best plan, not just
>the table metadata. Index selectivity is very important, and the size
>of the tables as well, I think.
>

I have checked this same query against a few databases. All are using
the slow plan except the one. They are all various sizes (all smaller
than the ones mentioned above).

> A database with 10 property headers
>and 100 sale headers could produce a different plan from a database
>with the opposite number of records.
>
>What I would recommend you to try, is the following query:
>
> Select a.SaleID, a.PropID, a.saleprice
> From SalesHeader a
> INNER JOIN PropertyHeader b on b.propid = a.propid
> Where b.MuniID = 302 and
> (a.SaleDate+0 Between '01/01/2003' and '12/31/2003') and
> (a.Improved Between 1 and 1) and
> a.USESALE_QK = 'Y' and
> b.valuationmethod <> 9 and
> b.enabled = 1
>
>This prevents the SALESHEADER_IX_SALEDATE index from being used
>altogether (and maybe even improve your performance regardless of plan
>chosen).
>

This caused the query to go through the entire SalesHeader table instead
of just the 1,000 records or so that it needed to. It got slower. I
get the best response with:

Select a.SaleID, a.PropID, a.saleprice
From SalesHeader a
LEFT OUTER JOIN PropertyHeader b on b.propid = a.propid
Where b.MuniID = 302 and
(a.SaleDate Between '01/01/2003' and '12/31/2003') and
(a.Improved Between 1 and 1) and
a.USESALE_QK = 'Y' and
b.valuationmethod <> 9 and
b.enabled = 1


The query goes from 15 or 20 seconds to virtually instantaneous.

Thanks anyhow.

Todd

>
>Set
>
>--- In firebird-support@yahoogroups.com, "Todd Brasseur" wrote:
>
>
>>Hi,
>>
>>I am at a loss with this problem ... any help is appreciated.
>>
>>I am running Firebird Version Firebird-1.5.1.4481-Win32.exe.
>>
>>I have two databases that I believe to be the same. I have looked
>>
>>
>at
>
>
>>the metadata both manually and using a metadata compare utility in
>>Quickdesk.
>>
>>I am running the same query against the two databases but the plan
>>that is being used is different between the two databases. I want
>>to understand this because this affects the performance of my
>>application tremendously.
>>
>>Here is the query:
>>
>>Select a.SaleID, a.PropID, a.saleprice
>> From SalesHeader a
>> INNER JOIN PropertyHeader b on b.propid = a.propid
>> Where b.MuniID = 302 and
>> (a.SaleDate Between '01/01/2003' and '12/31/2003') and
>> (a.Improved Between 1 and 1) and
>> a.USESALE_QK = 'Y' and
>> b.valuationmethod <> 9 and
>> b.enabled = 1
>>
>>
>>One database uses the plan:
>>
>>PLAN JOIN (A INDEX (SALESHEADER_IS_USE,SALESHEADER_IX_SALEDATE),B
>>INDEX (RDB$PRIMARY59))
>>
>>The other database uses the plan:
>>
>>PLAN JOIN (B INDEX (RDB$FOREIGN152,PROPERTY_IX_ENABLED),A INDEX
>>(RDB$FOREIGN202,SALESHEADER_IX_SALEDATE))
>>
>>
>>The main difference as I see it is that one has the primary table as
>>the main index and the other has the secondary table as the primary
>>index. This means that one query goes through alot more records
>>than the other one.
>>
>>As a side note ... I found that if I change the query to:
>>
>>Select a.SaleID, a.PropID, a.saleprice
>> From SalesHeader a
>> LEFT OUTER JOIN PropertyHeader b on b.propid = a.propid
>> Where b.MuniID = 302 and
>> (a.SaleDate Between '01/01/2003' and '12/31/2003') and
>> (a.Improved Between 1 and 1) and
>> a.USESALE_QK = 'Y' and
>> b.valuationmethod <> 9 and
>> b.enabled = 1
>>
>>both databases use the plan:
>>
>>PLAN JOIN (A INDEX (SALESHEADER_IS_USE,SALESHEADER_IX_SALEDATE),B
>>INDEX (RDB$PRIMARY59))
>>
>>What can I look at to determine the difference between the two
>>databases? I have compared the metadata for the two tables involved
>>in the query and it is exactly the same.
>>
>>Thanks in advance,
>>
>>Todd E. Brasseur
>>COMPASS Municipal Services Inc.
>>
>>
>
>
>
>
>
>Yahoo! Groups Links
>
>
>
>
>
>
>
>
>

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