June 23, 2009 at 9:31 am
I have the following code and the I/O is horrible. I can not think of a way to shrink it down, I thought about using a cursor or loop, but i do not think it is the right thing to do. your help is greatly appreciated. The reference cash has 3+mill rows.
SELECT MONTH(CASE WHEN cash.DaysOut = 'Payment already posted'
THEN cash.OrgPayDate
WHEN cash.DaysOut = 'Future payment date'
THEN CASE WHEN ( SELECT WKend
FROM market.dbo.tbl_Calendar
WHERE market.dbo.tbl_Calendar.ercdate = cash.OrgPayDate
) = 0
THEN cash.OrgPayDate
ELSE ( SELECT TOP 1
market.dbo.tbl_Calendar.ercdate
FROM market.dbo.tbl_Calendar
WHERE market.dbo.tbl_Calendar.WkEnd = 0
AND market.dbo.tbl_Calendar.ercdate > cash.OrgPayDate
ORDER BY ErcDate
)
END
ELSE cash.OrgPayDate
END) AS Mth,
YEAR(CASE WHEN cash.DaysOut = 'Payment already posted'
THEN cash.OrgPayDate
WHEN cash.DaysOut = 'Future payment date'
THEN CASE WHEN ( SELECT WKend
FROM market.dbo.tbl_Calendar
WHERE market.dbo.tbl_Calendar.ercdate = cash.OrgPayDate
) = 0
THEN cash.OrgPayDate
ELSE ( SELECT TOP 1
market.dbo.tbl_Calendar.ercdate
FROM market.dbo.tbl_Calendar
WHERE market.dbo.tbl_Calendar.WkEnd = 0
AND market.dbo.tbl_Calendar.ercdate > cash.OrgPayDate
ORDER BY ErcDate
)
END
ELSE cash.OrgPayDate
END) AS Yr,
PremNo
FROM cash
INNER JOIN .InvoiceDetail ON cash.InvoiceID = .InvoiceDetail.InvoiceID
INNER JOIN .Premise ON .invoicedetail.premid = .Premise.premid
June 23, 2009 at 12:01 pm
Thoses correlated sub-queries inside the case statements are probably leading to Loop Join operations across the three million rows worth of data. Generally, but not always, you're better off dealing with extra data of this sort using joins to pull the data together. Probably, you'd still need to use sub-selects, but you can use those sub-selects as derived tables instead of as correlated queries to make it work.
Instead of trying to determine if you need data and then querying against it, run the query against the full data set and return what you have. This means joining the cash table to the tbl_Calendar table, probably twice, to the value where the ercdate & OrgPayDate are not a weekend and when they are (if I'm interpreting the code correctly). In fact, you should be able to get the month & year from single queries, on each because you're not going for different years from the months associated with the pay dates, right?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply