none
怎么优化成语句二的执行计划? RRS feed

  • 问题

  • 执行计划:

    declare @i int
    set @i=1
    
    set statistics profile on
    
    --语句一:
    select * from tUser order by case when @i>1 then UserName end
    
    --语句二:
    select * from tUser
    
    set statistics profile off

     

    case when @i>1 then UserName end,结果是null,即是order by null,那么order by不起作用才是呀(应该会自动优化),怎么还排序了,怎么优化成语句二的执行计划?

    select * from tUser order by case when @i>1 then UserName end
      |--Sort(ORDER BY:([Expr1003] ASC))
           |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [@i]>(1) THEN [Test].[dbo].[tUser].[UserName] ELSE NULL END))
                |--Clustered Index Scan(OBJECT:([Test].[dbo].[tUser].[PK_tUser]))


    select * from tUser
      |--Clustered Index Scan(OBJECT:([Test].[dbo].[tUser].[PK_tUser]))

    2010年6月21日 10:43

答案

  • select * from tUser order by case when UserName='a' then 1 else 2 end 分析器在分析时,可以判断出无论条件怎样,是order by 1 或 order by 2. 这两个结果都是无意义的order by, 所以优化器应该可以判断不用考虑order by。不过在我的试验中,看见优化器还是有排序这个计划。你的查询计划中没有order by的计划吗?

    如我前面所说,即使优化结果有order by null, 在执行中基本也不会占用时间和资源,所以总的执行效率应该也不会有差别。 不过有待你那边实际的验证,呵呵。


    This posting is provided "AS IS" with no warranties, and confers no rights.

    • 已标记为答案 SQL STUDIO 2010年6月23日 14:50
    2010年6月23日 10:58

全部回复

  • declare @i int
    set @i=1

    select * from tUser where @i=1

    似乎也不能优化成

    select * from tUser

    这种计划

    2010年6月21日 11:18
  • 1.注意虽然 case when @i>1 then UserName end 的结果是null,但是 order by case when @i>1 then UserName end 的意思不是 order by null 。而是 order by 某列X,这个列X的值是null。理解这个有助于明白sql 的执行计划为什么会这么做(没有按照你想象的方式优化)。

    2.对于@1=1的where条件,如果条件是true那么执行和不加条件的执行计划类似,sql 在这里明显是可以做到优化的。

    你可以对比一下几条语句的查询计划

    select * from sysobjects
    
    select * from sysobjects where 1=1
    
    declare @a int
    set @a=1
    select * from sysobjects where @a=1
    
    select *,case when @a>1 then name end as t from sysobjects order by case when @a>1 then name end
    
    
    

    family as water
    2010年6月21日 13:56
  • 不懂 Stone Z 的意思,不过将UserName改成1(即下面语句) ,相同都是 order by null,不过计划就变成 “语句二的执行计划”,我想是因为引用了外部列,查询计划就不懂优化了???莫非是查询计划在“case when @i>1 then UserName end”中找到外部列(非常数表达式UserName),就不运算@i>1这样的常数表达式,导致不能优化

    select * from tUser order by case when @i>1 then 1 end

     

    如果是下面的情况,真令人烦恼,本以为当@Var1=0或@Var2=0时,可以跳过一些排序得到优化呢!(函数又不能用sp_executesql)

    create function getVar(@Var1 int,@Var2 int) returns int as
    begin
    	declare @Value int
    	select top 1 @Value=Value from tVar
    	order by
    	case when @Var1>0 then Var1 end desc,
    	case when @Var2>0 then Var2 end asc
    	return @Value
    end
    

     

    2010年6月21日 14:24
  • SQL Server查询的步骤首先是查询优化(Query Optimization),然后是查询执行(Query Execution). 在做查询优化是只通过系统的一些设置(是否有clustered索引等等)和统计值(记录值的分布等等)分析,优化和生成本条SQL语句的查询计划 -- 也就是一条语句是一个优化树,而不会考虑前一条语句的条件。你变量中的条件是否满足是在后一步的查询执行这一步才知道,所以不会反应到查询计划。不过,我认为即使查询计划不一样,两条语句在执行的时间上不应该有太大区别,因为order by null在执行时差不多不花资源,你测试过两条语句在执行时间上的差别吗?
    This posting is provided "AS IS" with no warranties, and confers no rights.
    • 已标记为答案 SQL STUDIO 2010年6月23日 9:39
    • 取消答案标记 SQL STUDIO 2010年6月23日 10:01
    2010年6月23日 8:49
  • 但是如果当成不确定性(order by的值不是固定的)时,例如:select * from tUser order by case when UserName='a' then 1 else 2 end

    时,情况就不一样了吧?

    虽说order by null,这个null,我们认为是一定的结果,但SqlServer分析当成不一定结果时,效率会变差吧?

    正如:select * from tUser order by case when @i>1 then 1 end这句,分析器会跳过order by分析成select * from tUser

    select * from tUser order by case when @i>1 then UserName end,就不是分析成select * from tUser

    2010年6月23日 10:07
  • select * from tUser order by case when UserName='a' then 1 else 2 end 分析器在分析时,可以判断出无论条件怎样,是order by 1 或 order by 2. 这两个结果都是无意义的order by, 所以优化器应该可以判断不用考虑order by。不过在我的试验中,看见优化器还是有排序这个计划。你的查询计划中没有order by的计划吗?

    如我前面所说,即使优化结果有order by null, 在执行中基本也不会占用时间和资源,所以总的执行效率应该也不会有差别。 不过有待你那边实际的验证,呵呵。


    This posting is provided "AS IS" with no warranties, and confers no rights.

    • 已标记为答案 SQL STUDIO 2010年6月23日 14:50
    2010年6月23日 10:58
  • 你看错了吧,UserName是tUser的字段不是变量,不是无意义的order by
    2010年6月23日 14:04
  • declare @i int
    set @i=1
    
    select * from tUser t
     order by
     case when @i>1 then t.UserName else '1111111111111111111111111111111111111111111111' end asc,
     case when @i>1 then t.UserName else '1111111111111111111111111111111111111111111112' end desc,
     case when @i>1 then t.UserName else '1111111111111111111111111111111111111111111113' end asc,
     case when @i>1 then t.UserName else '1111111111111111111111111111111111111111111114' end desc,
     case when @i>1 then t.UserName else '1111111111111111111111111111111111111111111115' end asc,
     case when @i>1 then t.UserName else '1111111111111111111111111111111111111111111116' end desc,
     case when @i>1 then t.UserName else '1111111111111111111111111111111111111111111117' end asc,
     case when @i>1 then t.UserName else '1111111111111111111111111111111111111111111118' end desc,
     case when @i>1 then t.UserName else '1111111111111111111111111111111111111111111119' end asc
    
    --用上面语句经过测试,相差好几秒,只能说是没有被优化,只是order by的数据长度为0,效率相差不大而已。
    2010年6月23日 14:49