SQL Performance-wise, what's better: an IF…ELS

问题:

I have a stored procedure that has a optional parameter, @UserID VARCHAR(50). The thing is, there are two ways to work with it:

1. Give it a default value of NULL, the have an IF...ELSE clause, that performs two different SELECT queries, one with 'WHERE UserID = @UserID' and without the where.
2. Give it a default value of '%' and then just have the where clause use 'WHERE UserID LIKE @UserID'. In the calling code, the '%' wont be used, so only exact matches will be found.

The question is: Which option is faster? Which option provides better performance as the table grows? Be aware that the UserID column is a foreign key and is not indexed.

EDIT: Something I want to add, based on some answers: The @UserID parameter is not (necessarily) the only optional parameter being passed. In some cases there are as many as 4 or 5 optional parameters.

回答1:

What I typically do is something like

WHERE ( @UserID IS NULL OR UserID = @UserID )

And why isn't it indexed? It's generally good form to index FKs, since you often join on them...

If you're worried about query plan storage, simply do: CREATE PROCEDURE ... WITH RECOMPILE

回答2:

The only way to tell for sure is to implement both and measure. For reference there is a third way to implement this, which is what I tend to use:

WHERE (@UserID IS NULL OR UserId = @UserId)

回答3:

SQL Server 2005 and subsequent have something called "statement-level recompilation". Check out http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

Basically, each individual statement executed by the query processor gets it's own optimized plan, which is then stored in "Plan Cache" (This is why they changed the name from "Procedure-Cache")

So branching your T-SQL into separate statements is better...

回答4:

Why not use:

where @UserID is null or UserID=@UserID 

+ on maintainability and performance

回答5:

The issue with having only one stored procedure is as mentioned quite well above that the SQL stores a compiled plan for the procedure, a plan for null is quite different to one with a value.

However, creating an if statement in the stored procedure will lead to the stored procedure being recompiled at run time. This may also add to the performance issues.

As mentioned elsewhere, this is suitable for a test and see approach, taking into account the if statement, an @UserID is null and two separate procedures.

Unfortunately, the speed of these approaches is going to vary greatly based on the amount of data and the frequency of the calls where the parameter is null vs the calls where the parameter is not. Again the number of parameters is also going to affect the efficacy of an approach that requires re-writing the procedures.

If you are using SQL 2005, you may get some mileage from the query plan hint option.

Correction: Sql 2005 and since has "statement-Level Recompilation" which store separate plans in cache for each statement in a procedure... So the old Pre-2005 policy of not putting multiple logic branch statements into a single stored procedure is no longer true... – Charles Bretana (i figure this was important enough to elevate from a comment)

回答6:

I'd defiantly go with the first because although it's less 'clever' it's easier to understand what's going on and will hence be easier to maintain.

The use of the special meaning default is likely to trip you up later with some unintended side-effect (documentation as to why you're using that default and it's usage is likely to be missed by any maintainer)

As to efficiency - unless you're looking at 1,000 users or more then it's unlikely to be sufficient an issue to override maintainability.

回答7:

First, you should create an index for UserID if you use it as a search criteria in this way.

Second, comparing UserID LIKE @UserID cannot use an index, because the optimizer doesn't know if you will give a @UserID parameter value that begins with a wildcard. Such a value cannot use the index, so the optimizer must assume it cannot create an execution plan using that index.

So I recommend:

1. Create an index on UserID
2. Use the first option, WHERE UserID = @UserID, which should be optimized to use the index.

edit: Mark Brady reminds me I forgot to address the NULL case. I agree with Mark's answer, do the IF and execute one of two queries. I give Mark's answer +1.

回答8:

Replace the single stored procedure with two. There's way to much room for the query optimizer to start whacking you with unintended consequence on this one. Change the client code to detect which one to call.

I bet if you had done it that way, we wouldn't need to be having this dialog.

And put an index on userid. Indexes are in there for a reason, and this is it.

回答9:

I would sort-of go with option 1, but actually have two stored procedures. One would get all the users and one would get a specific user. I think this is clearer than passing in a NULL. This is a scenario where you do want two different SQL statements because you're asking for different things (all rows vs one row).