x

Case Statement Doubles time to render results

I added a case statement to a view using CTE's
After the case statement my transaction time went from 3:17 seconds to 5:54

Is there a way to diagnose and rewrite this more efficiently?

    (CASE WHEN cdp1.PRC = 0 and cdp1.PRC_TYP_ID = 41 Then ssf.PRC Else cdp1.PRC END)+
    (CASE WHEN cdp1.PRC = 0 and cdp1.PRC_TYP_ID = 43 Then ssf.PRC Else cdp1.PRC END)+
    (CASE WHEN cdp1.PRC = 0 and cdp1.PRC_TYP_ID = 124 Then ssf.PRC Else cdp1.PRC END)
AS PRC
more ▼

asked Dec 28, 2010 at 10:00 AM in Default

siera_gld gravatar image

siera_gld
1k 79 84 85

I rewrote the case statement like this and got processing time down to 5:11 but this still is not optimal...

(CASE WHEN cdp1.PRC = 0 and cdp1.PRC_TYP_ID in (41, 43, 124) Then ssf.PRC Else cdp1.PRC END)
Dec 28, 2010 at 10:08 AM siera_gld
As Grant has mentioned the reason for this is something outside the code that you have posted. May be the way you are joining to the CTE? post the execution plan and we will be able to help a lot more...
Dec 29, 2010 at 02:26 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
In order to troubleshoot what is occuring, you should post the execution plan of the query. Preference would be for the actual plan, but even the estimated plan would be useful. Just looking at this statement completely divorced from the rest of the query, it's just not possible to tell you why it's running slow.
more ▼

answered Dec 28, 2010 at 10:30 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x41
x32

asked: Dec 28, 2010 at 10:00 AM

Seen: 993 times

Last Updated: Dec 29, 2010 at 02:24 AM