怎样提高这语句的效率

Question

本帖最后由 zzxap 于 2012-11-20 17:12:56 编辑

Answer 1

where中先写userid=132,然后再写not in，至于索引，应该不用说了吧。

Answer 2

把id 串处理成表 not exists 貌似可以

Answer 3

select * 
from Invoicing_Invoicing 
where charindex(','+cast(id as varchar(10))+',',','+'712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,774,1052,1053,1054,1055,1056,1057,1059,1061,1062,1065,1067,1068,1069,1070,1071,1087,1088,1089,1091,1092,1093,1095,1097,1098,1099,1100,1638,1640,1641,1642,1878,1879,1880,1881,1882,1883,1884,1885,1886'+',')>0
and userid=132

Answer 4

楼上没考虑的头和尾的问题。。
我发个我自己写的Func吧。ALTER function [dbo].[Func_Contains]        
(        
@Source varchar(200),        
@Temp varchar(200)        
)        
returns bit        
begin        
declare @i int      
set @i=0      
set @i=charindex(',',@Source)         
if(@i<1)        
begin        
   if (@Temp=@Source)      
   begin      
   return 1      
   end      
end      
else      
begin        
  if(@Temp =substring(@Source,0,charindex(',',@Source)))  
  return 1  
      if(charindex(','+@Temp+',',@Source)>0)         
      return 1              
       while (charindex(','+@Temp,@Source)>0) 
begin
 if(@Temp=substring(@Source,0,charindex(',',@Source)))
 return 1
 set @Source=substring(@Source,charindex(',',@Source)+1,len(@Source))
end
if(@Temp=@Source)
 return 1
end    
return 0        
end 

Answer 5

不好意思，刚才发的有点问题。
我觉得考虑效率的话可以用charindexof去判断是否存在。
判断有3中情况：
1.开头匹配
2.中间匹配
3.结尾匹配
把3种情况考虑进去就行了。

Answer 6

首先，where的顺序是有严格限定的，从左到右顺序执行，如果userid有索引的话，就会先大面积筛选掉不必要的数据，值剩下132的数据，假设你有1000万数据，而132的userid只有10000条的话，那你第一步筛选后就剩下了10000条，也就是一下子去掉了999万条数据。然后对这10000条再筛选那一大串的in。速度不会太慢。但是如果你where第一步是notin ，本身not in就容易导致全表扫描，再加上in里面的字段可能分布很零散。这样也会导致索引无效而选择表扫描，随着数量的增大。扫描的开销就越来越大。最后筛选完not In的数据，可能还有个900万，那么你要在900万里面再筛选userid=132的，那效率肯定低。有点乱，不知道你看不看得懂

Answer 7

谢谢，看的懂，只是不清楚SQL的执行顺序！

Answer 8

调换一下where顺序在低版本的SQLSERVER可能很有用处，究竟调换后有没有用处要看具体的执行计划。

Answer 9

见笑了
确实不知道where条件的顺序是否会影响查询的性能，自己随便测试了一下，有不对的地方还望指正，create table tableTestWhere
(
StaffID int identity(1,1),
Department varchar(50),
Salary decimal(18,3),
age int,
Level varchar(20),
Re varchar(36)
)create unique clustered index IX_ID on tableTestWhere(StaffID)
declare @i int 
set @i=0
while @i<100000
begin
declare @d varchar(200),@l varchar(10)
--随便搞个部门职位啥的字段
if(@i<3000)
begin
set @d='A' 
set @l='X1'
end
else 
begin
set @d='B' 
set @l='X2'
end
--工资年龄正比，不成听天由命，O(∩_∩)O~
insert into tableTestWhere values (@d,ceiling(10000*rand()),ceiling(40*rand()),@l,newid())
set @i=@i+1;
endset statistics profile on--聚集索引条件在前在后，执行计划是一样的
select * from tableTestWhere 
where  
staffid<100
and re in ('F4835A18-3859-4218-990D-1AAC2847AC54','EE9391EB-FD3B-4F38-BFBA-CFEC65AD7828')--select * from tableTestWhere   where   re in ('F4835A18-3859-4218-990D-1AAC2847AC54','EE9391EB-FD3B-4F38-BFBA-CFEC65AD7828')  and staffid<100
  |--Clustered Index Seek(OBJECT:([DEVTEST].[dbo].[tableTestWhere].[IX_ID]), SEEK:([DEVTEST].[dbo].[tableTestWhere].[StaffID] < (100)),  WHERE:([DEVTEST].[dbo].[tableTestWhere].[Re]='EE9391EB-FD3B-4F38-BFBA-CFEC65AD7828' OR [DEVTEST].[dbo].[tableTestWhere].[Re]='F4835A18-3859-4218-990D-1AAC2847AC54') ORDERED FORWARD)
  
  
select * from tableTestWhere 
where   
re in ('F4835A18-3859-4218-990D-1AAC2847AC54','EE9391EB-FD3B-4F38-BFBA-CFEC65AD7828')
and staffid<100  --select * from tableTestWhere   where  staffid<100    and re in ('F4835A18-3859-4218-990D-1AAC2847AC54','EE9391EB-FD3B-4F38-BFBA-CFEC65AD7828')
  |--Clustered Index Seek(OBJECT:([DEVTEST].[dbo].[tableTestWhere].[IX_ID]), SEEK:([DEVTEST].[dbo].[tableTestWhere].[StaffID] < (100)),  WHERE:([DEVTEST].[dbo].[tableTestWhere].[Re]='EE9391EB-FD3B-4F38-BFBA-CFEC65AD7828' OR [DEVTEST].[dbo].[tableTestWhere].[Re]='F4835A18-3859-4218-990D-1AAC2847AC54') ORDERED FORWARD)

Answer 10

考虑使用  not  exists来替换掉not  in  你应该真实情况不是写那么一串id吧  是不是传过来的一个字符串？如果是传来的一个字符串的话  那你的写法也存在问题

Answer 11

记得某版主说过
where条件的顺序是不会影响查询的性能，sql会自动优化
exists貌似和in的性能貌似一样的，没具体测试过

Answer 12

这个要看SQLServer的版本，高级版本会优化，但是低级就不清楚了。我手上的《SQLServer 2005 效能调教》（胡百敬）7.1.2 勿负向查询。也就是说尽量（当然没办法就没办法了）不要使用not in not exists,<>这些。会导致表扫描。另外，即使SQLServer会优化where的顺序，但是明显增加了开销。