“IF..ElseIf..Else”或“Where子句”指导存储过程结果
我有以下两个 SQL 语句
第一个：
IF(@User_Id IS NULL)
BEGIN
SELECT *
FROM [UserTable]
END
ELSE
BEGIN
SELECT *
FROM [UserTable] AS u
WHERE u.[Id] = @User_Id
END
第二个：
SELECT *
FROM [UserTable] AS u
WHERE (@User_Id IS NULL OR u.[Id] = @User_Id)
这两个查询都将包装在其自己的存储过程中。我怀疑 IF 语句导致 SQL 上的大量重新编译。我面临着要么将 IF 语句的每个部分分离成自己的存储过程，要么用 WHERE 子句替换整个 IF 语句（如上面第二个 SQL 语句所示）
我的问题是：这两个语句之间有什么区别从性能角度来看，SQL 将如何处理每个语句？
谢谢。
I have the following two SQL statements
First one:
IF(@User_Id IS NULL)
BEGIN
SELECT *
FROM [UserTable]
END
ELSE
BEGIN
SELECT *
FROM [UserTable] AS u
WHERE u.[Id] = @User_Id
END
Second one:
SELECT *
FROM [UserTable] AS u
WHERE (@User_Id IS NULL OR u.[Id] = @User_Id)
Both of those queries would be wrapped in its own stored procedure. I am suspecting that the IF statement is causing a lot of recompilations on SQL. I am faced with either separating each part of the IF statement into its own stored procedure, OR replacing the entire IF statement with a WHERE clause (illustrated above in the second SQL statement)
My question is: What is the difference between the two statements from a performance perspective, and how would SQL treat each statement?
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
两种解决方案都会生成相同数量的编译。
第一个解决方案是查询优化器可以自由地为两个不同的查询中的每一个提出最佳计划。第一个查询（在 IF 的 NULL 分支上）没有太多可优化的地方，但第二个查询（在 ID 的 NOT NULL 分支上）可以优化，如果
Id列上有索引存在。但第二种解决方案是一场优化灾难。无论@User_Id 参数的值是什么，优化器都必须提出一个适用于该参数的任何值的计划。因此，无论
@User_Id的值如何，该计划都将始终使用次优表扫描。这个问题没有办法解决，这不是某些人可能认为的参数嗅探。只是计划的正确性，即使计划生成时的值不为空，即使参数为 NULL，计划也必须工作，因此它不能使用Id上的索引。总是，总是，总是，使用带有显式
IF的第一种形式。Both solution will generate identical number of compilations.
The first solution the query optimizer is free to come up with the best plan for each of the two, different, queries. The first query (on the NULL branch of the IF) is not much that can be optimized, but the second one (on the NOT NULL branch of the ID) can be optimized if an index on
Idcolumn exists.But the second solution is an optimization disaster. No matter the value of the
@User_Idparameter, the optimizer has to come up with a plan that works for any value of the parameter. As such, no matter the value of@User_Id, the plan will always use the suboptimal table scan. There is just no way around this issue, and this is not parameter sniffing as some might think. Is just correctness of the plan, even if the value at plan generation time is NOT NULL, the plan has to work even when the parameter is NULL, so it cannot use the index onId.Always, always, always, use the first form with the explicit
IF.