x

Use Case or split into four procedures?

Hi So, I have the SP below which updates the Exec_Code, based on the Policy Type. I'm wondering should I split this into four different procedures instead? So that I could have SP 1 as

SET Exec_Code = PC_MAP WHERE (PolType = PC) AND (PC_Map <> '0000')

Or should I get the same performance from using the Case statement as below?

CREATE PROCEDURE dbo.PersLines_ByDir AS SET NOCOUNT ON UPDATE dbo.Monthly_Pre

SET Exec_Code = CASE PolType WHEN 'PC' THEN CASE WHEN PC_Map = '0000' THEN Exec_Code ELSE PC_Map END

WHEN 'HC' THEN CASE WHEN HC_Map = '0000' THEN Exec_Code ELSE HC_Map END

WHEN 'HQ' THEN CASE WHEN HC_Map= '0000' THEN Exec_Code ELSE HC_Map END

WHEN 'HI' THEN CASE WHEN HI_Map= '0000' THEN Exec_Code ELSE HI_Map END

END

FROM blahblah WHERE PolType In ('PC','HC','HQ','HI');
more ▼

asked Jan 26, 2010 at 08:00 AM in Default

Diarmuid gravatar image

Diarmuid
63 2 2 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

It's an it depends, I'm afraid. If poltype is indexed, then you may find that you get the best performance from four separate update statements, but if it is not then your case statement would reduce scanning. I don't see the need for four separate stored procedures, however, I would simply put four update statements in the ome procedure...

more ▼

answered Jan 26, 2010 at 08:05 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

+1 - I'm with Matt, this logic isnt so complicated as to be easier to maintain/understand in seperate procs, indeed I think the opposite would be true, splitting it would be more confusing. Test, test and test again to find out which is the best for performance if that is your primary concern.
Jan 26, 2010 at 08:08 AM Fatherjack ♦♦
Thanks. I think I'll leave it as is, with the Case, just from the maintainance point of view.
Jan 26, 2010 at 08:36 AM Diarmuid
(comments are locked)
10|1200 characters needed characters left

I would probably leave it as is, but I'd suggest testing it by passing different values to the procedure, but flush the cache between each call and check to see if the execution plan changes radically between each call. If there are no or only minor changes, you're probably OK, but if there are radical changes, possibly even scans, table spools, what have you, then you may need to break it up.

more ▼

answered Jan 26, 2010 at 11:35 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
101k 19 21 74

(comments are locked)
10|1200 characters needed characters left

I would do it in one. I'm not a fan of IN because it generally uses a scan, but doing it with a single procedure should be at least as quick as four calls. CASE is plenty fast enough.

more ▼

answered Jan 26, 2010 at 08:06 AM

Rob Farley gravatar image

Rob Farley
5.7k 16 18 20

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

x729
x474

asked: Jan 26, 2010 at 08:00 AM

Seen: 1726 times

Last Updated: Jan 26, 2010 at 08:00 AM