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.

The differences between the two databases may be the data itself?
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. 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).

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.