OK, I'm really confused now.

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

Without the index it worked in 3 of 4 installations? BTW, I found a feature
called "Database Validation" in IBExpert and it reported no problems on the
problem database.

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?

Here is the query again:

SELECT DISTINCT
DOCTOR.DOCTORID,
DOCTOR.LASTNAME,
DOCTOR.FIRSTNAME,
DOCTOR.MIDDLEINITIAL,
PATIENT.PATIENTID,
PATIENT.CHARTID,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.MIDDLEINITIAL
FROM
PATIENT
INNER JOIN BILLING ON (PATIENT.PATIENTID = BILLING.PATIENTID)
INNER JOIN BILLINGITEM ON (BILLING.BILLINGID = BILLINGITEM.BILLINGID)
LEFT OUTER JOIN PAYMENT ON (BILLING.BILLINGID = PAYMENT.BILLINGID)
LEFT OUTER JOIN REFUND ON (BILLING.BILLINGID = REFUND.BILLINGID)
INNER JOIN DOCTOR ON (BILLING.DOCTORID = DOCTOR.DOCTORID)
WHERE
BILLINGITEM.BILLINGITEMDATE = :BILLINGITEMDATE OR
PAYMENT.PAYMENTDATE = :BILLINGITEMDATE OR
REFUND.REFUNDDATE = :BILLINGITEMDATE
GROUP BY
DOCTOR.DOCTORID,
DOCTOR.LASTNAME,
DOCTOR.FIRSTNAME,
DOCTOR.MIDDLEINITIAL,
PATIENT.PATIENTID,
PATIENT.CHARTID,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.MIDDLEINITIAL
HAVING
SUM(PAYMENT.AMOUNT) <> 0 OR
SUM(BILLINGITEM.AMOUNT * BILLINGITEM.UNITS) <> 0 OR
SUM(REFUND.AMOUNT) <> 0
ORDER BY
DOCTOR.LASTNAME,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME



_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Anthony Tanas
Sent: Wednesday, November 22, 2006 11:18 AM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] How to diagnose this problem?



Well the data will naturally be different on each database so I'm not sure
that comparing data would help me.

I removed Distinct that did not help, but you are right it's not needed
anyway.

The plan is:

PLAN SORT (SORT (JOIN (JOIN (JOIN (JOIN (PATIENT NATURAL,BILLING INDEX
(FK_BILLING_PATIENT),BILLINGITEM INDEX (FK_BILLINGITEM_BILLING)),PAYMENT
NATURAL),REFUND NATURAL),DOCTOR INDEX (DOCTOR_PRIMARY))))

Would gfix help me? I'm going to read the chapter on it from The Firebird
Book. Also I own a full version of IBExpert I wonder if it has something to
help me.

Thanks,

Anthony

From: firebird-support@ <mailto:firebird-support%40yahoogroups.com>
yahoogroups.com
[mailto:firebird-support@ <mailto:firebird-support%40yahoogroups.com>
yahoogroups.com] On Behalf Of Milan Babuskov
Sent: Tuesday, November 21, 2006 4:44 PM
To: firebird-support@ <mailto:firebird-support%40yahoogroups.com>
yahoogroups.com
Subject: Re: [firebird-support] How to diagnose this problem?

Anthony Tanas wrote:

> I've compared the databases to make sure they are the same...that leaves

the

> only difference as the data itself! How can I figure out where the problem
> is? :(

You can use FBCopy (or some similar tool) to compare the data as well.

> The problem must be in the data since it is only one location but in case

it

> may help here is the simplest of the queries that does not return:
>
> SELECT DISTINCT

...

> GROUP BY

I never had a need to use DISTINCT if I have GROUP BY already in there,
GROUP BY already groups records together. Try to remove DISTINCT from
the query.

BTW, what is the plan for that query?

> A side question - if I kill the client the CPU on the server still runs at
> 100% untill I kill the firebird server. Will the firebird server ever give
> up on the query or will it bog down the server untill firebird is

restarted?

AFAIK, it will keep working until it tries to fetch the first record and
then it will cancel it.

--
Milan Babuskov
http://swoes. <http://swoes. <http://swoes.blogspot.com/> blogspot.com/>
blogspot.com/
http://www.flamerob <http://www.flamerob <http://www.flamerobin.org> in.org>
in.org

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







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