Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
3  
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 '12 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. –  Aaron Bertrand Jul 18 '12 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 '12 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 '12 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. –  srini.venigalla Jul 18 '12 at 15:25
add comment

1 Answer 1

up vote 3 down vote accepted

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

share|improve this answer
    
+1 on the concurrency issue. –  Thomas Stringer Jul 19 '12 at 14:41
    
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. –  Martin Smith Jul 19 '12 at 14:53
    
@MartinSmith: I haven't looked too deeply recently, thanks –  gbn Jul 19 '12 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 '13 at 18:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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