where 条件中的IIF函数或是case when语句的性能问题怎么优化？

Question

有这样的应该场景，用户可以根据不同的日期自由切换（创建日期，记账日期）查询，在界面上根据单选按钮选择不同的日期类别查询。

存储过程里面这样写：（换成case when语句效果一样）

WHERE IIF(@datetype=0,CreatedDate,OrderDate) BETWEEN @sdate AND @edate

这样写之后不走索引，性能急剧下降。CreatedDate,OrderDate这两个字段都是有索引的，如何优化？

优化的前提条件是（1）不能拼接SQL，接拼SQL会带来其它性能问题并且不好维护。（2）不能用if else 语句判断@datetype的值写两段脚本，因为一个存储过程里面就会有好几处这样的代码，至少上百个存储过程，这样写增加不小的工作量而且不好维护。

还有什么办法优化吗？

Answer 1

这个你还是拼语句吧， 用参数，不要直接把值拼到 sql 语句里面，把逻辑放到 sql 语句里面并不代表性能就好

比如 WHERE IIF(@datetype=0,CreatedDate,OrderDate) BETWEEN @sdate AND @edate
可以这样
if @datetype = 0
 set @sql =@sql +  'where CreatedDate BETWEEN @sdate AND @edate'
else
 set @sql = @sql + 'where OrderDate BETWEEN @sdate AND @edate'
exec sp_executesql @sql, N'@sdate datetime, @edate datetime', @sdate , @edate

Answer 2

通常程序里面拼会比存储过程里面拼好一些，存储过程里面拼，用 profile 看到的语句还是无法确定具体执行的是什么，需要根据参数值走一次

程序里面拼，传递给 sql server 的是具体执行的 sql, 可以明确知道在执行什么

Answer 3

因为这都是报表业务，存储过程比较长，没有程序，没用CLR，所以没在程序里面拼接。 如果拼接起来里面还有很多单引号、其它参数、#开头的临时表，不好处理，可读性也不好。

写if else 不用拼接的方法可读性也不好，脚本会变得好长也不好维护，稍不注意开发人员容易改错。

我换成这样的写法，效果也不好，有时候比WHERE IIF(@datetype=0,CreatedDate,OrderDate) BETWEEN @sdate AND @edate快，有时候又比这个慢

WHERE CreatedDate BETWEEN (CASE WHEN @datetype=0 THEN @sdate ELSE CreatedDate END) AND (CASE WHEN @datetype=0 THEN @edate ELSE CreatedDate END)
AND OrderDate BETWEEN (CASE WHEN @datetype=1 THEN @sdate ELSE OrderDate END) AND (CASE WHEN @datetype=1 THEN @edate ELSE OrderDate END)

我现在想到的办法是写两个存储过程，一个存储过程用创建日期，一个日期用记账日期，改好一个存储过程直接复制一份替换对应的字段这样减少出错机会，外面再套一个存储过程进行日期类别逻辑判断

看看还有没有别的方法？

Answer 4

where 条件里面做判断使用什么字段，性能通常是最不好的

如果你这个条件就是日期字侧面的问题，当然可以用两个存储过程， 但是更多需要判断呢？那也就章法着更多的存储过程么？ 从问题追溯的角度来讲，用多个存储过程更容易知道调用的是那个逻辑

但纯粹从性能来讲，用 sp_executesql做参数传递与调用存储过程的方式是差不多的，两者都有预编译的，仅参数值不同的重复调用通常是不会再编译的

Answer 5

有得就有失。想要提高性能有时候就是要在程序写法甚至逻辑上做下调整。优化就是在性能和其他需求直接进行平衡。

Answer 6

这种情况其实比较常见，通常开发人员都喜欢开发一个大而全的查询界面，什么样的条件都可以设置，然后与之对应的就是这种很多判断之后出来的查询

但真正通过监控查询调用可以发现，其实最常用的就那么几种情况