Use Case or split into four procedures?

Question

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');

Answer 1

0

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

answered Jan 26 '10 at 08:05 AM

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 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 '10 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 '10 at 08:36 AM Diarmuid

(comments are locked)

Answer 2

0

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.

answered Jan 26 '10 at 11:35 AM

Grant Fritchey ♦♦
62.1k ● 12 ● 20 ● 66

(comments are locked)

Answer 3

0

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.

answered Jan 26 '10 at 08:06 AM

Rob Farley
5.7k ● 13 ● 17 ● 19

(comments are locked)

Use Case or split into four procedures?

Follow this question

Related Questions