尽可能避免在where条件或者join时的on条件中使用case函数(T-SQL)

有时候我们为了使SQL语句看起来尽可能的简洁而尽可能避免在where条件或者join时的on条件中使用case函数。

例如：

--CREATE TABLE DateTest

--(Id int identity primary key,

--date1 datetime,

--date2 datetime

--)

 

--SET NOCOUNT ON

--DECLARE @i INT

--SET @i=0

 

--WHILE @i<=20000

--BEGIN

--    INSERT INTO DateTest(date1,date2)

--    SELECT @i,@i

--    SET @i = @i+1

--END

 

--CREATE INDEX idxDate1 ON DateTest(date1)

 

--CREATE INDEX idxDate2 ON DateTest(date2)

 

DECLARE

      @beginDate DATETIME,

      @endDate   DATETIME

     

DECLARE

      @searchType INT

 

SET @searchType = 1

 

SET @beginDate = 0

SET @endDate = 100

 

SET STATISTICS TIME ON

 

--写法1

IF    @searchType = 1 --第一种查询按date1查询

      SELECT *

      FROM DateTest

      WHERE date1 BETWEEN @beginDate AND @endDate

ELSE IF @searchType = 2 --第一种查询按date2查询

      SELECT *

      FROM DateTest

      WHERE date2 BETWEEN @beginDate AND @endDate

 

----写法2,这样写似乎更简洁漂亮

SELECT *

FROM DateTest

WHERE (CASE WHEN @searchType = 1 THEN date1

                  ELSE date2

            END ) BETWEEN @beginDate AND @endDate

     

查看执行计划如下：

执行计划显示似乎第一种查询耗时是第二种的2倍。

看起来第二种似乎既简洁又高效。

然而实际结果恰恰相反。读者可以自己测试看看。

再看一个在JOIN的ON条件中使用case的例子。

CREATE TABLE ta

(

      col1 INT PRIMARY KEY,

      col2 INT,

      col3 INT

)

 

CREATE TABLE tb

(

      col1 INT PRIMARY KEY,

      col2 INT,

      col3 INT,

      col4 INT,

      col5 INT,

      col6 INT

)

 

DECLARE @i INT

SET @i=0

 

WHILE @i<=70000

BEGIN

      INSERT INTO ta(col1,col2,col3)

      SELECT @i,@i,@i

 

      SET @i = @i+1

END

 

 

SET @i=0

 

WHILE @i<=500

BEGIN

      INSERT INTO tb(col1,col2,col3,col4,col5,col6)

      SELECT 2*@i, 3*@i, 4*@i, 5*@i, 6*@i, 7*@i

 

      SET @i = @i+1

END

 

CREATE INDEX idxcol3 ON ta(col3) include(col1,col2)

 

CREATE INDEX idxcol2 on ta(col2)

 

--语句1

SELECT DISTINCT a.col1,a.col2

FROM ta     a                       -- 记录 7W

      JOIN tb b               -- 记录 500

ON CASE WHEN a.col3=0 THEN a.col1 ELSE a.col2 END

  IN (b.col1,b.col2,b.col3,b.col4,b.col5,b.col6)

 

 

--改写成等价语句2

SELECT DISTINCT a.col1,a.col2

FROM ta     a                 -- 记录 7W

WHERE a.col3=0

      AND a.col1 IN (   select col1  FROM tb

                              UNION ALL

                              SELECT col2  FROM tb

                              UNION ALL

                              SELECT col3  FROM tb

                              UNION ALL

                              SELECT col4  FROM tb

                              UNION ALL

                              SELECT col5  FROM tb

                              UNION ALL

                              SELECT col6  FROM tb

                  )

UNION ALL

SELECT DISTINCT a.col1,a.col2

FROM ta     a                 -- 记录 7W

WHERE a.col3 <> 0

      AND a.col2 IN (   select col1  FROM tb

                              UNION ALL

                              SELECT col2  FROM tb

                              UNION ALL

                              SELECT col3  FROM tb

                              UNION ALL

                              SELECT col4  FROM tb

                              UNION ALL

                              SELECT col5  FROM tb

                              UNION ALL

                              SELECT col6  FROM tb

                         )

在笔者的开发环境中比较语句1和语句2发现，语句执行需要20多秒，而语句2执行耗时不足1秒。   

结论：    

在where条件或者join时的on条件中，应该尽可能避免使用case函数，从而避免不必要的性能问题。