October 3, 2003 at 12:10 pm
I've got a stored procedure on SQL Server 2000 that just doesn't seem to want to run. I'm pretty sure it has to do with the way the serer is set up and maybe specifically with the tempdb. I'd appreciate any guidance or direction on solving the problem.
The sproc looks like this ...
/** usp_ar_dso **/
CREATE PROCEDURE usp_ar_dso
@AsOfDate datetime = NULL,
@account varchar(255) = '%'
AS
-- get defaults and convert Crystal wildcard
SET @AsOfDate = datediff(day, 0, coalesce(@AsOfDate, CURRENT_TIMESTAMP))
SET @account = REPLACE(@Account, '*', '%')
SELECTAR.Transaction_Type as Transaction_Type
, AR.Transaction_UID as Transaction_UID
, AR.InvoiceID as InvoiceID
, AR.LicenseID as LicenseID
, AR.Name as Name
, AR.Due AS DueDate
, AR.EOD_DATE AS InvoiceDate
, AR.Amount AS InvoiceAmount
, COALESCE(SUM(ARPayments.Amount_Paid),0) AS PaymentAmount
, AR.Amount - SUM(COALESCE (ARPayments.Amount_Paid, 0)) AS BalanceDue
FROMAR LEFT OUTER JOIN ARPayments
ON AR.Transaction_UID = ARPayments.Transaction_UID
and ARPayments.Date <= @AsOfDate
WHERE0=0
and AR.DUE <= @AsOfDate
and AR.NAME like @account
GROUP BYAR.Transaction_Type
, AR.Transaction_UID
, AR.InvoiceID
, AR.LicenseID
, AR.Name
, AR.Due
, AR.EOD_DATE
, AR.Amount
HAVING(AR.Transaction_Type IN ('Credit', 'Invoice'))
AND (AR.Amount - SUM(COALESCE (ARPayments.Amount_Paid, 0)) <> 0)
ORDER BY AR.Name, AR.LicenseID, AR.Due, AR.InvoiceID
return
/** end of usp_ar_dso **/
On executing this sproc, the system usually just sits and churns. If I stop the sproc after a couple minutes, I might get a few rows returned but not all. If I take out the aggregates, the GROUP BY and the HAVING clauses, it runs fine.
I've checked the tempdb for blocking processes and don't see any. I'm sure it has to do with the set up on my client's machine. This runs fine on my development box at my office.
Any ideas on where to start looking for fixes?
David
October 6, 2003 at 6:57 am
I still do not understand well your select but I have some issues with that:
- Why do you have this interresting where clause: "WHERE 0=0"
- you should remove (AR.Transaction_Type IN ('Credit', 'Invoice')) condition from the HAVING part and put into the WHERE part.
(Notice the HAVING part operates on the result returned by GROUP BY i.e. it makes a postselection, while the WHERE part make a preselection)
Could you change that after tell us if it works?
Bye
Gabor
Bye
Gabor
October 6, 2003 at 11:18 am
Thanks Gabor ...
To answer your questions ...
1) the "WHERE 0=0" is a construct I use to help format the code a little and make it easier to comment out lines of the WHERE clause.
2) Thanks for the tip on the HAVING vs WHERE> I have done that and testing continues. Right now I'm spending time tuning the indexes. I'm thinking that mught do the trick.
David
October 7, 2003 at 10:25 am
Dave,
For that particular query I would define an index like this:
create clustered index I1 on AR(Transaction_Type, NAME, DUE DESC)
or
create clustered index I1 on AR(Transaction_Type, NAME, DUE DESC, Transaction_UID)
and at least
create index I1 on ARPayments(Transaction_UID)
or better
create index I1 on ARPayments(Transaction_UID, Amount_Paid) --This is just to avoid a bookmark lookup
Bye
Gabor
Bye
Gabor
October 13, 2003 at 10:06 am
dplittle, have you had any success so far?
Two things came to my mind when looking at your code:
1) your WHERE clause is weak, i.e. the "like" part does not allow use of indexes and the <=Date Part probably is not a strong limitation.
2) If your query runs in acceptable time without grouping, would it be possible to put ungrouped values into a temp table and group in a second step? Even though this ist not a good production solution, it may show you better where the problem is (make a print time between the steps)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply