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

avatar image

siera_gld
1k 82 88 93

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

avatar image

Grant Fritchey ♦♦
137k 20 42 81

(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.

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:

x39
x12

asked: Dec 28, 2010 at 10:00 AM

Seen: 1126 times

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

Copyright 2016 Redgate Software. Privacy Policy