CODEWITHSUNDEEP
sql-serverstored-procedures
mint
mint

Reputation: 3433

MS SQL Server - Stored Procedure Efficiency

I currently have a single stored procedure that runs a command similiar to this:

UPDATE TABLE
SET STATUS = '02'
WHERE ID IN (SELECT ID FROM ANOTHER TABLE WHERE STATUS IN ('01'))

UPDATE TABLE
SET STATUS = '05'
WHERE ID IN (SELECT ID FROM ANOTHER TABLE WHERE STATUS IN ('08'))

UPDATE TABLE
SET STATUS = '06'
WHERE ID IN (SELECT ID FROM ANOTHER TABLE WHERE STATUS IN ('04'))

This is a made up procedure but mimics what our real application does. Would it be more performant if I broke each UPDATE statement into its own stored procedure and then just executed each procedure within my initial SP such as:

EXEC UpdateStatus02

EXEC UpdateStatus05

EXEC UpdateStatus06

Or will this ultimately be equivalent?

Thank you for your insight.

Upvotes: 0

Views: 58

Answers (2)

Eric Brandt
Eric Brandt

Reputation: 8101

The distinction between executing the UPDATE statements in one procedure or three is likely to be negligible.

However, to echo the comments, one statement is likely to be more performant than three statements.

Much more importantly, though, is to limit the UPDATE so it's only over-writing values that need to be updated via a WHERE clause. Otherwise, it's very likely that for a large number of records you'll be writing to disk to set the value of STATUS to the same value that's already there. That will kill performance.

You're also likely to get better speed from a JOIN than from a sub-select with an IN.

I'd start by tweaking something like this to your situation and seeing if you get better speed with one call rather than three.

UPDATE tgt
SET tgt.STATUS = CASE WHEN src.STATUS IN ('01') THEN '02'
                      WHEN src.STATUS IN ('08') THEN '05'
                      WHEN src.STATUS IN ('04') THEN '06'
FROM
  TABLE AS tgt
  JOIN
  ANOTHER_TABLE AS src
    ON 
      src.ID = tgt.ID
      AND
      src.STATUS IN ('01','04','08')
WHERE 
  tgt.STATUS <>  CASE WHEN src.STATUS IN ('01') THEN '02'
                      WHEN src.STATUS IN ('08') THEN '05'
                      WHEN src.STATUS IN ('04') THEN '06';

Edit: Nod to Guido's answer for limiting the number of records pulled from ANOTHER_TABLE with the IN clause there.

Upvotes: 1

GuidoG
GuidoG

Reputation: 12014

I think doing all 3 updates in one statement would be faster.
You can do that using a join and a case statement, see below example

update t
set    Status = case when t2.Status = '01' then '02'
                     when t2.Status = '08' then '05'
                     when t2.Status = '04' then '06'
                end
from   table t
  inner join another table t2
    on t2.id = t.id
where t2.Status in ('01', '08', '04')

Upvotes: 3

Related Questions