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.