is this query efficient

G

Gary B

Is this query as efficient as it could be, especially the "WHERE" clause?

PARAMETERS DATETO Text ( 22 );
SELECT CUSTOMERS.CUSTNO, (CUSTOMERS.LNAME+", "+CUSTOMERS.FNAME) AS CUSTNAME,
AgingSummary_Charges.CURCHRG-AgingSummary_Payments.CURPAID AS CUR,
AgingSummary_Charges.THIRCHRG-AgingSummary_Payments.THIRPAID AS THIR,
AgingSummary_Charges.SIXCHRG-AgingSummary_Payments.SIXPAID AS SIX,
AgingSummary_Charges.NINECHRG-AgingSummary_Payments.NINEPAID AS NINE,
AgingSummary_Charges.TOTALCHRG-AgingSummary_Payments.TOTALPAID AS TOTAL,
FROM (CUSTOMERS LEFT JOIN AgingSummary_Charges ON CUSTOMERS.CUSTOMERSKEY=
AgingSummary_Charges.CUSTOMERSKEY) LEFT JOIN AgingSummary_Payments ON
CUSTOMERS.CUSTOMERSKEY = AgingSummary_Payments.CUSTOMERSKEY
WHERE ( (AgingSummary_Charges.TOTALCHRG-AgingSummary_Payments.TOTALPAID) <>
0 )
ORDER BY (CUSTOMERS.LNAME+", "+CUSTOMERS.FNAME);
 
J

John Vinson

Is this query as efficient as it could be, especially the "WHERE" clause?

PARAMETERS DATETO Text ( 22 );
SELECT CUSTOMERS.CUSTNO, (CUSTOMERS.LNAME+", "+CUSTOMERS.FNAME) AS CUSTNAME,
AgingSummary_Charges.CURCHRG-AgingSummary_Payments.CURPAID AS CUR,
AgingSummary_Charges.THIRCHRG-AgingSummary_Payments.THIRPAID AS THIR,
AgingSummary_Charges.SIXCHRG-AgingSummary_Payments.SIXPAID AS SIX,
AgingSummary_Charges.NINECHRG-AgingSummary_Payments.NINEPAID AS NINE,
AgingSummary_Charges.TOTALCHRG-AgingSummary_Payments.TOTALPAID AS TOTAL,
FROM (CUSTOMERS LEFT JOIN AgingSummary_Charges ON CUSTOMERS.CUSTOMERSKEY=
AgingSummary_Charges.CUSTOMERSKEY) LEFT JOIN AgingSummary_Payments ON
CUSTOMERS.CUSTOMERSKEY = AgingSummary_Payments.CUSTOMERSKEY
WHERE ( (AgingSummary_Charges.TOTALCHRG-AgingSummary_Payments.TOTALPAID) <>
0 )
ORDER BY (CUSTOMERS.LNAME+", "+CUSTOMERS.FNAME);
Click to expand...

You can do better. If there are indexes (nonunique) on TOTALCHRG,
TOTALPAID, LNAME and FNAME consider using

WHERE AgingSummary_Charges.TotalChrg <>
AgingSummary_Payments.TotalPaid
ORDER BY Customers.LNAME, Customers.FNAME;

As it is, you're searching and sorting by *calculated expressions*
rather than by fields - and thereby defeating any benefit of indexing
on your table fields.

John W. Vinson[MVP]
 
Top