Anthony Tanas wrote:

> I simply added an Index for PAYMENT.BILLINGID and the query works. WHY?

I don't know how big are the tables, but index can make a HUGE difference.

> Without the index it worked in 3 of 4 installations?

You are joining 5 tables. If index is not used/available it just
multiplies the problem on such query. You can easily write a statement
that would take server a month to complete. Perhaps those 3 or 4
installations don't have too many record in that critical table OR you
have a lot of garbage records. Have you tried to sweep the problematic
database?

> BTW, I found a feature
> called "Database Validation" in IBExpert and it reported no problems on the
> problem database.

I believe it validates the database structure, not efficiency of your
queries.

> Again how could simply adding an index make a query that never returned (I
> allowed it to run for nearly 30 minutes one time) all of a sudden work fine?

I know of some indexes that changed time for some reports from 2 hours
to 2 seconds. Indexes were invented for a good purpose.

BTW, I really don't see why you use OUTER joins? They can make
preformance really bad.

LEFT OUTER JOIN PAYMENT
...
WHERE
PAYMENT.PAYMENTDATE = :BILLINGITEMDATE

If there is no matching record in PAYMENT than PAYMENT.PAYMENTDATE is
NULL, and I don't think you are sending NULL as BILLINGITEMDATE? Even if
you were, you'd have to use IS NULL and not equal sign.

So, change all your joins to simple JOIN and try.

--
Milan Babuskov
http://swoes.blogspot.com/
http://www.flamerobin.org