question

Diarmuid avatar image
Diarmuid asked

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');
sqlsql-server-2000
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

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

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rob Farley avatar image
Rob Farley answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.