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

avatar image

Diarmuid
63 2 2 4

(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

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(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

avatar image

Grant Fritchey ♦♦
137k 20 42 81

(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

avatar image

Rob Farley
5.8k 16 21 28

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

x985
x501

asked: Jan 26, 2010 at 08:00 AM

Seen: 1924 times

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

Copyright 2016 Redgate Software. Privacy Policy