5

I have read that procs that do not always do the same thing per se, will not always have a good plan generated. That is (correct me if I'm wrong), if I have a proc that if the day is even it reads from table X otherwise it reads from table Y and the first time it executes is even, then the plan generated will be optimized for reading from table X and not Y, and sql will use that plan even when reading from Y, that imho is simple to understand and try to avoid, but what about procedures that act on the same table.

Like the sample below (obvioulsy a very simple example), if the first time it runs, and the item does exist, and subsequent runs generally do not (or vice-versa), would I end up with a "not so great" plan which would affect performance?

CREATE PROCEDURE dbo.MyProc
(
@data    VarChar(25),
@name    VarChar(25)
)

AS

    IF NOT EXISTS (SELECT * FROM dbo.MyTable WHERE myname = @name)
    BEGIN
        INSERT dbo.MyTable (myname, mydata)
        VALUES (@name, @data)
    END
    ELSE
    BEGIN
        UPDATE dbo.MyTabel
        SET mydata = @data
        WHERE myname = @name
    END
5
  • 4
    If this is all there is, you have other problems that need to be addressed first. If you can use 2008+ syntax, switch to MERGE.
    – Jon Seigel
    Jul 18, 2012 at 15:03
  • If there is an index on myname, and if myname is unique, there really shouldn't be a way to generate a bad plan if the procedure really is this simple. However I agree with @Jon - this pattern is very problematic unless you only ever have one user. Jul 18, 2012 at 15:18
  • @JonSeigel thank you for info on MERGE, I wasn't aware of it, but yes, my question is not strictly about the insert/update, I'm really interested in whether sql server would be able to generate a good execution plan for something like that, it's merely a simple example
    – Jason
    Jul 18, 2012 at 15:20
  • It's really, really hard to say for sure if SQL Server will generate a good plan when the exact syntax is unknown. Even the tiniest detail can drastically change the behaviour.
    – Jon Seigel
    Jul 18, 2012 at 15:24
  • @jason - you are thinking procedural. when in DB, think relational. what you are doing is a merge, so get it done via one statement. Jul 18, 2012 at 15:25

1 Answer 1

5

SQL Server 2005+ has statement level recompilation. Therefore, IIRC you'll get an optimal plan depending on branch.

However, your code isn't concurrency-safe. Two overlapping calls can get true for the NOT EXISTS: you get an error. This is why you use MERGE on SQL Server 2008.

Or assorted JDFI patterns for older versions. Also see Need Help Troubleshooting Sql Server 2005 Deadlock Scenario which has several options: it depends on your load too

3
  • In this case both branches are likely to get a trivial plan anyway assuming myname is found by a unique index. Statement level recompile only helps if the statement is subject to a deferred compile or a recompile though. Otherwise all statements will be compiled according to the parameter values in the first invocation even if that statement was not actually executed. Jul 19, 2012 at 14:53
  • @MartinSmith: I haven't looked too deeply recently, thanks
    – gbn
    Jul 19, 2012 at 15:14
  • I ran into an unexpected error with the MERGE keyword recently, I haven't had a chance to confirm my suspicion on an SQL Server 2012 db, perhaps you can help answer my other question...stackoverflow.com/q/18796408/167304
    – Jason
    Sep 17, 2013 at 18:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.