help me in optimizing this query

Hi,

I have a result set like this ..

ACCOUNTS DATEBILLED DUEDATE TOTALBALANCE PRIORBALANCE NEWBALANCE PAYMENTAMOUNT PAYNC
-- - - - -
2001 05/24/2007 06/21/2007 826.88 826.88 0.00 0.00 0.00
2001 05/24/2007 06/22/2007 826.88 826.88 0.00 0.00 0.00
2001 05/24/2007 06/23/2007 826.88 826.88 0.00 0.00 0.00

2002 05/24/2007 06/22/2007 826.88 826.88 0.00 0.00 0.00
2002 05/24/2007 06/24/2007 826.88 826.88 0.00 0.00 0.00

and the query is this..

select A.AccountNo as Accounts, A.DATEBILLED as DATEBILLED, A.DUEDATE as DUEDATE, A.TOTALBALANCE, A.PRIORBALANCE,(A.TOTALBALANCE.PRIORBALANCE) as NEWBALANCE,
A.PAYMENTAMOUNT,
(case when (A.PAYMENTAMOUNT-A.PRIORBALANCE) > 0 then A.PAYMENTAMOUNT - A.PRIORBALANCE else 0 end) as PAYNC
from ACCTPAID A , ACCTTYPE B
where A.DUEDATE BETWEEN '2007-06-19' AND '2007-06-30' and (A.TOTALBALANCE - A.PRIORBALANCE) >=0
and A.ACCOUNTNO = B.ACCOUNTNO
and A.duedate=(select max(duedate) from ACCTPAID
where ACCTPAID.accountno = A.accountno)

It is taking like 30 minutes to execute ONLY because of the query "select max(duedate) from ACCTPAID where ACCTPAID.accountno = A.accountno" without that it is taking like 2 minutes..

Is there a way to reduce this so that i can pick only the maximum due dates for each account..

Any help would be greatly appreciated

Thanks,
Praveen

How can we help if we have no idea about those table's estructure. Can you provide DDL, including constraints and indexes, sample data and expected result?

AMB

|||

Please find the DDLS for the two tables. there are no indexes....

ACCTPAID

Column Type
name name Length Scale Nulls
-- --
ACCOUNTNO DECIMAL 13 0 No
DATEBILLED DATE 4 0 No
DUEDATE DATE 4 0 No
TOTALBALANCE DECIMAL 13 2 No
PRIORBALANCE DECIMAL 13 2 No
PAYMENTAMOUNT DECIMAL 13 2 No

ACCTTYPE

Column Type
name name Length Scale Nulls
-- --
ACCOUNTNO DECIMAL 13 0 No
DIVISIONNO DECIMAL 2 0 No
ACCTTYPE CHARACTER 1 0 No

Thanks,

Praveen Raju

|||

It would help us better assist you if you could include table DDL, query strategy used so far, sample data in the form of INSERT statements, and an illustration of the desired results

Click here for information that will explain how to prepare your question so that the 'volunteers' here can optimally spend their time assisting you.

|||

Try this

Code Snippet

Select

A.AccountNo as Accounts

, A.DATEBILLED as DATEBILLED

, A.DUEDATE as DUEDATE

, A.TOTALBALANCE

, A.PRIORBALANCE

,(A.TOTALBALANCE.PRIORBALANCE) as NEWBALANCE

, A.PAYMENTAMOUNT

,(case when (A.PAYMENTAMOUNT-A.PRIORBALANCE) > 0 then

A.PAYMENTAMOUNT - A.PRIORBALANCE

else 0

end) as PAYNC

From

ACCTPAID A

Join ACCTTYPE B On A.ACCOUNTNO = B.ACCOUNTNO

Where

A.TOTALBALANCE >= A.PRIORBALANCE

and DUEDATE = (Select max(duedate) duedate from ACCTPAID as MaxDate

Where MaxDate.accountno=A.accountno And DUEDATE Between '2007-06-19' AND '2007-06-30')

|||

What about primary key and foreign key constraints?

Based in the query, I will suggest to create indexes in both tables by [ACCOUNTNO], and also a clustered index on table [ACCTPAID] by [DUEDATE]. Try with these changes to see if the performance improves. Post back the execution plan.

AMB

|||

Hi Manivannan,

Thanks for your query...by changing the position of the "DUEDATE Between '2007-06-19' AND '2007-06-30'" from the initial to the last line, the query execution time reduced from 15 minutes to less than a minute...

I dont know why but this has a great help from you and thanks a lot...

Praveen Raju