当前位置: 代码迷 >> Sql Server >> 使用CASE作查询条件后,如何速度变得很慢,应怎样解决
  详细解决方案

使用CASE作查询条件后,如何速度变得很慢,应怎样解决

热度:66   发布时间:2016-04-27 16:36:08.0
使用CASE作查询条件后,怎么速度变得很慢,应怎样解决?
Select   *   from   vwOutHouseDsp
where   iDocStatus <=100   and   dDocDate> [email protected]   and   dDocDate <[email protected]
and   (cDocType= '销售发货 '   or   cDocType= '销售退货 ')
and   WhSysCode=case   when   @tcWhSysCode= ' '   then   WhSysCode   else   @tcWhSysCode   End  
and   ISysCode=case   when   @tcISysCode= ' '   then   ISysCode   else   @tcISysCode   End  
and   IBSysCode=case   when   @tcIBSysCode= ' '   then   IBSysCode   else   @tcIBSysCode  

以上这句在WHERE上使用CASE后查速度就变得很慢了,如果把
and   WhSysCode=case   when   @tcWhSysCode= ' '   then   WhSysCode   else   @tcWhSysCode   End  
and   ISysCode=case   when   @tcISysCode= ' '   then   ISysCode   else   @tcISysCode   End  
and   IBSysCode=case   when   @tcIBSysCode= ' '   then   IBSysCode   else   @tcIBSysCode  
这几句去掉,速度就正常了,怎么会这样呢?
另外,如果不用CASE,这种情况的条件查询怎样解决比较好呢?


------解决方案--------------------
把条件
@tcWhSysCode,@tcISysCode,@tcIBSysCode 默认设成null
create procedure protest
@tcWhSysCode int =null,
@tcISysCode int =null,
@tcIBSysCode int =null
as
Select * from vwOutHouseDsp
where iDocStatus <=100 and dDocDate> [email protected] and dDocDate <[email protected]
and (cDocType= '销售发货 ' or cDocType= '销售退货 ')
and WhSysCode=isnull(@tcWhSysCode,WhSysCode)
and ISysCode=isnull(@tcISysCode,ISysCode)
and IBSysCode=isnull(@tcIBSysCode,IBSysCode)
  相关解决方案