SQL Server - 条件语句的查询执行计划

How do conditional statements (like IF ... ELSE) affect the query execution plan in SQL Server (2005 and above)?

条件语句(如IF ... ELSE)如何影响SQL Server(2005及更高版本)中的查询执行计划?

Can conditional statements cause poor execution plans, and are there any form of conditionals you need to be wary of when considering performance?

条件语句是否会导致执行计划不佳,在考虑性能时是否需要警惕任何形式的条件?

** Edited to add ** :

**编辑添加**:

I'm specifically referring to the cached query execution plan. For instance, when caching the query execution plan in the instance below, are two execution plans cached for each of the outcomes of the conditional?

我特指的是缓存的查询执行计划。例如,在下面的实例中缓存查询执行计划时,是否为条件的每个结果缓存了两个执行计划?

DECLARE @condition BIT

IF @condition = 1
BEGIN
    SELECT * from ...
END
ELSE
BEGIN
    SELECT * from ..
END

1 个解决方案

#1

You'll get plan recompiles often with that approach. I generally try to split them up, so you end up with:

你会经常用这种方法重新编译计划。我通常会尝试拆分它们,所以你最终得到:

DECLARE @condition BIT

IF @condition = 1 
BEGIN 
 EXEC MyProc1
END 
ELSE 
BEGIN 
 EXEC MyProc2
END

This way there's no difference to the end users, and MyProc1 & 2 get their own, proper cached execution plans. One procedure, one query.

这种方式与最终用户没有区别,MyProc1和2获得了他们自己的,适当的缓存执行计划。一个程序,一个查询。

#1