高人帮我看看下面俩个语句为什么执行效率差这么多

Question

一Declare @textbook_tb_id varchar(100)
Declare @str_length int
Declare @str_precis     varchar(100)
set @textbook_tb_id='200500020002'
set @str_length=12
set @str_precis='1,2,3,4'
select id,name 
from textbook_tb 
where creat_id in(
select distinct substring(knowledge_tb.creat_id,1,@str_length) as creat_id
from grade_textbook_tb INNER JOIN knowledge_tb
 on substring(grade_textbook_tb.textbook_creat_id,1,@str_length)=
substring(knowledge_tb.creat_id,1,@str_length)
 INNER JOIN dic_knowledge_point_tb 
 on dic_knowledge_point_tb.id = knowledge_tb.knowledge_point_id
where knowledge_tb.creat_id like 
  (
   select creat_id+'%' as creat_id 
   from textbook_tb 
   where id=@textbook_tb_id
  ) 
  and len (knowledge_tb.creat_id)>=@str_length
  and dic_knowledge_point_tb.if_del = '0' 
  and knowledge_tb.flag='0'
  and charindex(','+rtrim(dic_knowledge_point_tb.require)+',',','+@str_precis+',')>0
)
and textbook_tb.flag = '0'
二Declare @textbook_tb_id varchar(100)
Declare @str_precis     varchar(100)
set @textbook_tb_id='200500020002'
set @str_precis='1,2,3,4'
select id,name 
from textbook_tb 
where creat_id in(
select distinct substring(knowledge_tb.creat_id,1,12) as creat_id
from grade_textbook_tb INNER JOIN knowledge_tb
 on substring(grade_textbook_tb.textbook_creat_id,1,12)=
substring(knowledge_tb.creat_id,1,12)
 INNER JOIN dic_knowledge_point_tb 
 on dic_knowledge_point_tb.id = knowledge_tb.knowledge_point_id
where knowledge_tb.creat_id like 
  (
   select creat_id+'%' as creat_id 
   from textbook_tb 
   where id=@textbook_tb_id
  ) 
  and len (knowledge_tb.creat_id)>=12
  and dic_knowledge_point_tb.if_del = '0' 
  and knowledge_tb.flag='0'
  and charindex(','+rtrim(dic_knowledge_point_tb.require)+',',','+@str_precis+',')>0
)
and textbook_tb.flag = '0'只是差了一个参数@str_length

Answer 1

len (knowledge_tb.creat_id)>=@str_length这种条件列带函数的写法,索引无效,扫全表的

Answer 2

我试了，把这个地方写成len (knowledge_tb.creat_id)>=12，还是不成，效率还是低

Answer 3

一
Declare @textbook_tb_id     varchar(100)
Declare @str_length int
Declare @str_precis     varchar(100)
set @textbook_tb_id='200500020002'
set @str_length=12
set @str_precis='1,2,3,4'
select id,name 
from textbook_tb 
where creat_id in(
         select distinct substring(knowledge_tb.creat_id,1,@str_length) as creat_id
from grade_textbook_tb 
              INNER JOIN knowledge_tb
     on substring(grade_textbook_tb.textbook_creat_id,1,@str_length)=
        substring(knowledge_tb.creat_id,1,@str_length)
              INNER JOIN dic_knowledge_point_tb 
     on dic_knowledge_point_tb.id = knowledge_tb.knowledge_point_id
         where knowledge_tb.creat_id like           (
 select creat_id+'%' as creat_id 
                                      from textbook_tb            where id=@textbook_tb_id
) 
      and len (knowledge_tb.creat_id)>=@str_length
      and dic_knowledge_point_tb.if_del = '0' 
      and knowledge_tb.flag='0'
      and charinde(','+rtrim(dic_knowledge_point_tb.require+',',','+
                            @str_precis+',')>0
     )
         and textbook_tb.flag = '0'
二Declare @textbook_tb_id     varchar(100)
Declare @str_precis     varchar(100)
set @textbook_tb_id='200500020002'
set @str_precis='1,2,3,4'
select id,name 
from textbook_tb 
where creat_id in(
select distinct substring(knowledge_tb.creat_id,1,12) as creat_id
from grade_textbook_tb 
                       INNER JOIN knowledge_t
                       on substring(grade_textbook_tb.textbook_creat_id,1,12)=
        substring(knowledge_tb.creat_id,1,12)
     INNER JOIN dic_knowledge_point_tb 
    on dic_knowledge_point_tb.id = knowledge_tb.knowledge_point_id
where knowledge_tb.creat_id like         (  select creat_id+'%' as creat_id 
                                               from textbook_tb   where id=@textbook_tb_id                                    ) 
      and len (knowledge_tb.creat_id)>=12
      and dic_knowledge_point_tb.if_del = '0' 
      and knowledge_tb.flag='0'
      and charindex(','+rtrim
                            (dic_knowledge_point_tb.require)  +',',','+   @str_precis+',')>0
           )
              and textbook_tb.flag = '0'只是差了一个参数@str_length

Answer 4

len (knowledge_tb.creat_id)>=@str_length
len (knowledge_tb.creat_id)>=12关键是前面的len 函数影响了效率条件语句带函数,索引无效

Answer 5

我这两个语句不同的地方只是多了个@str_length，可是下面的执行速度非常快，上面的执行速度就非常慢

Answer 6

len (knowledge_tb.creat_id)>=@str_length
len (knowledge_tb.creat_id)>=12
2条语句效率绝对是一样,你把2条分开执行多测试几次你再比较一下2条语句的执行计划

Answer 7

恩，这两句的执行效率是一样，问题好像出现在上面
select distinct substring(knowledge_tb.creat_id,1,12) as creat_id
from grade_textbook_tb 
     INNER JOIN knowledge_t
     on substring(grade_textbook_tb.textbook_creat_id,1,12)=
        substring(knowledge_tb.creat_id,1,12)
     INNER JOIN dic_knowledge_point_tb  
     on dic_knowledge_point_tb.id = knowledge_tb.knowledge_point_idselect distinct substring(knowledge_tb.creat_id,1,@str_length) as creat_id
from grade_textbook_tb 
     INNER JOIN knowledge_t
     on substring(grade_textbook_tb.textbook_creat_id,1,@str_length)=
        substring(knowledge_tb.creat_id,1,@str_length)
     INNER JOIN dic_knowledge_point_tb  
     on dic_knowledge_point_tb.id = knowledge_tb.knowledge_point_id可是我不明白为什么多个参数执行效率就不一样了