optimizing SUM and CASE statements

Question

optimizing SUM and CASE statements

mattech06

SSCrazy

Points: 2484
More actions
May 27, 2015 at 7:41 am

#297247

Is there a way of improving the SUM statements below? When I run the query without them it's very fast but with them it takes ages, thanks,
SELECT
r.dbPatID, p.dbPatFirstName, p.dbPatLastName, r.dbstatusdesc, r.dbAddDate, r.LastName, r.dbStaffLastName
,
SUM(CASE WHEN t.LedgerAmount > 0 AND t.LedgerType !=29 AND t.LedgerType !=30 AND t.LedgerType != 31 AND t.LedgerType != 1 OR t.LedgerType = 16 THEN t.LedgerAmount ELSE 0.00 END) AS Charges,
SUM(CASE WHEN t.LedgerAmount < 0 AND t.LedgerType != 1 AND t.LedgerType != 16 AND t.LedgerType != 45 OR t.LedgerType = 29 OR t.LedgerType = 30 OR t.LedgerType = 31 THEN t.LedgerAmount ELSE 0.00 END) AS Payments,
SUM(CASE WHEN t.LedgerType = 1 OR t.LedgerType = 46 THEN t.LedgerAmount ELSE 0.00 END) * -1 AS Contracts
FROM
vw_ReferralKPIs r
LEFT JOIN Transactions t ON t.PatientID = r.dbPatID
AND r.ClientRef = t.ClientRef
LEFT JOIN Patient p ON p.dbPatID = r.dbPatID
AND r.ClientRef = p.ClientRef
WHERE
(r.dbAddDate >= '2014-01-01' OR '2014-01-01' = '')
AND (r.dbAddDate <= '2015-05-27 23:59' OR '2015-05-27 23:59' = '')
AND (r.dbStaffLastName IN ('') OR '' = '')
AND (r.LastName IN ('Tio ')) --OR 'Tio ' = '')
AND r.ClientRef = 'INV'
GROUP BY
r.dbPatID, p.dbPatFirstName, p.dbPatLastName, r.dbstatusdesc, r.dbAddDate, r.LastName, r.dbStaffLastName

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

mattech06 (5/27/2015)
Is there a way of improving the SUM statements below? When I run the query without them it's very fast but with them it takes ages, thanks,
SELECT
r.dbPatID, p.dbPatFirstName, p.dbPatLastName, r.dbstatusdesc, r.dbAddDate, r.LastName, r.dbStaffLastName
,
SUM(CASE WHEN t.LedgerAmount > 0 AND t.LedgerType !=29 AND t.LedgerType !=30 AND t.LedgerType != 31 AND t.LedgerType != 1 OR t.LedgerType = 16 THEN t.LedgerAmount ELSE 0.00 END) AS Charges,
SUM(CASE WHEN t.LedgerAmount < 0 AND t.LedgerType != 1 AND t.LedgerType != 16 AND t.LedgerType != 45 OR t.LedgerType = 29 OR t.LedgerType = 30 OR t.LedgerType = 31 THEN t.LedgerAmount ELSE 0.00 END) AS Payments,
SUM(CASE WHEN t.LedgerType = 1 OR t.LedgerType = 46 THEN t.LedgerAmount ELSE 0.00 END) * -1 AS Contracts
FROM
vw_ReferralKPIs r
LEFT JOIN Transactions t ON t.PatientID = r.dbPatID
AND r.ClientRef = t.ClientRef
LEFT JOIN Patient p ON p.dbPatID = r.dbPatID
AND r.ClientRef = p.ClientRef
WHERE
(r.dbAddDate >= '2014-01-01' OR '2014-01-01' = '')
AND (r.dbAddDate <= '2015-05-27 23:59' OR '2015-05-27 23:59' = '')
AND (r.dbStaffLastName IN ('') OR '' = '')
AND (r.LastName IN ('Tio ')) --OR 'Tio ' = '')
AND r.ClientRef = 'INV'
GROUP BY
r.dbPatID, p.dbPatFirstName, p.dbPatLastName, r.dbstatusdesc, r.dbAddDate, r.LastName, r.dbStaffLastName

Please post the "actual execution plan". Any suggestions are guesses without it.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Your CASE expressions don't look correct with multiple AND / OR with no defining parens.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mattech06 SSCrazy Points: 2484 More actions · Answer 3

Hi Chris,

what do you mean by actual execution plan?

The statement works - just if the result set (100+) is bigger than normal it takes over a minute whilst under that (~50) it's ok

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

mattech06 (5/27/2015)
Hi Chris,
what do you mean by actual execution plan?
The statement works - just if the result set (100+) is bigger than normal it takes over a minute whilst under that (~50) it's ok

I think the statement generates some incorrect results. Here's a handy test:

SELECT

f.LedgerAmount,

t.LedgerType,

Charges = (CASE WHEN f.LedgerAmount > 0 AND t.LedgerType !=29 AND t.LedgerType !=30 AND t.LedgerType != 31 AND t.LedgerType != 1 OR t.LedgerType = 16 THEN t.LedgerType ELSE 0 END),

Payments = (CASE WHEN f.LedgerAmount < 0 AND t.LedgerType != 1 AND t.LedgerType != 16 AND t.LedgerType != 45 OR t.LedgerType = 29 OR t.LedgerType = 30 OR t.LedgerType = 31 THEN t.LedgerType ELSE 0 END),

Contracts = (CASE WHEN t.LedgerType = 1 OR t.LedgerType = 46 THEN t.LedgerType ELSE 0 END)

FROM (

SELECT

LedgerType = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

FROM (VALUES (0),(0),(0),(0),(0),(0)) d (n),

(VALUES (0),(0),(0),(0),(0),(0),(0),(0)) e (n)

) t

CROSS APPLY (VALUES (-1),(1)) f (LedgerAmount)

ORDER BY f.LedgerAmount, t.LedgerType

-- Look at LedgerType 16, 29, 30, 31

Edit - made code easier to follow, fixed bug

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mattech06 SSCrazy Points: 2484 More actions · Answer 5

I've run that but what does it tell me or what am I expecting to see?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

mattech06 (5/27/2015)
I've run that but what does it tell me or what am I expecting to see?

Incorrect results. I'm genuinely surprised you can't see it.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mattech06 SSCrazy Points: 2484 More actions · Answer 7

mattech06

SSCrazy

Points: 2484

May 27, 2015 at 8:21 am

#1800947

I've been up since 3 this morning 🙂

mattech06 SSCrazy Points: 2484 More actions · Answer 8

in my sql I have THEN t.LedgerAmount not t.LedgerType, even so, I'm bamboozled right now I'm afraid

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Have a look at row 16 of my test set and compare it to your CASE statements.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mattech06 SSCrazy Points: 2484 More actions · Answer 10

after some caffeine input I can see the logic is wrong there, thanks

mattech06 SSCrazy Points: 2484 More actions · Answer 11

after some caffeine input I can see the logic is wrong there, thanks

mattech06 SSCrazy Points: 2484 More actions · Answer 12

I still need to figure out a way of optimizing it too I guess

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

Have some kip first 🙂

This article [/url]describes how to capture and send/attach a .sqlplan file (execution plan export file).

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mattech06 SSCrazy Points: 2484 More actions · Answer 14

mattech06

SSCrazy

Points: 2484

May 27, 2015 at 9:14 am

#1800977

ta 🙂