有时候我们为了使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函数,从而避免不必要的性能问题。