Subject | How to diagnose this problem? |
---|---|
Author | Anthony Tanas |
Post date | 2006-11-21T20:48:17Z |
First off thanks for the previous replies to other questions...I don't reply
with thanks if I have everything I need so as to not clutter this very busy
discussion forum, but I am allways grateful for the friendly help I receive
here!
Now my current problem: I have four beta testers of my software...at ONE
location I have a couple of complex queries that never return. They work
fine at the other locations.
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? :(
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
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
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?
with thanks if I have everything I need so as to not clutter this very busy
discussion forum, but I am allways grateful for the friendly help I receive
here!
Now my current problem: I have four beta testers of my software...at ONE
location I have a couple of complex queries that never return. They work
fine at the other locations.
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? :(
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
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
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?