SQL Server - 条件语句的查询执行计划 [英] SQL Server - Query Execution Plan For Conditional Statements

问题描述

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

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

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

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

推荐答案

您会经常使用这种方法重新编译计划.我通常会尝试将它们分开，因此您最终会得到:

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

这样对最终用户和 MyProc1 & 没有区别.2 获得自己的、适当的缓存执行计划.一个程序，一个查询.

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

这篇关于SQL Server - 条件语句的查询执行计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！