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

  • 问题

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

    存储过程里面这样写:(换成case when语句效果一样)

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

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

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

    还有什么办法优化吗?

    2015年7月21日 7:51

答案

  • 这个你还是拼语句吧, 用参数,不要直接把值拼到 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

    2015年7月21日 8:06
  • 通常程序里面拼会比存储过程里面拼好一些,存储过程里面拼,用 profile 看到的语句还是无法确定具体执行的是什么,需要根据参数值走一次

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

    2015年7月21日 8:08

全部回复

  • 这个你还是拼语句吧, 用参数,不要直接把值拼到 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

    2015年7月21日 8:06
  • 通常程序里面拼会比存储过程里面拼好一些,存储过程里面拼,用 profile 看到的语句还是无法确定具体执行的是什么,需要根据参数值走一次

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

    2015年7月21日 8:08
  • 因为这都是报表业务,存储过程比较长,没有程序,没用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)

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

    看看还有没有别的方法?

    2015年7月21日 9:42
  • where 条件里面做判断使用什么字段,性能通常是最不好的

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

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

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

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

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

    2015年7月23日 1:53