一句T-SQL语句引发的思考

测试总结：（我们可以比较关注：语句 2和语句 5）
1.在1百万条记录的情况下，语句 5是最快的，但在5千万条记录下是最慢的。这说明INDEX的优化一定的情况下，数据量不同，检索的效率也是不同的。我们平时在写T-SQL时一般关注的时INDEX的使用，只要我们写的T-SQL是利用CLUSTERED INDEX，我们就认为是最优化了，其实这是一个误区，我们还要关注Estimated row count的值，大量的I/O操作是我们应该关注的，所以我们应该根据数据量的不同选择相应的T-SQL语句，不要认为在小数据量下是最高的在大数据量的状态下也许是最慢的:-(。

2.在执行规划中最快的，并不是运行最快的，我们可以看在1百万（6列）在这行中，语句 2和语句 5的比例是0.81％：1.14％，但实际的运行效率是，38ms:11ms。所以，我们在选择T-SQL是要考虑本地I/O的速度，所以在优化语句时不仅要看执行规划还要计算一下具体的效率。

在测试的语句上加入：

    SET STATISTICS TIME  ON/OFF
    SET STATISTICS IO  ON/OFF
是一个很好的调试方法。

3.综合评价，语句 2的效率是最高的，执行效率没有随数据量变化而有很大的差别。

4.执行规划越简单的语句（语句1），综合效率越高，反之则越低（语句3，语句4）。

5.在平时写T-SQL语句时，一定要根据不同的数据量进行测试，虽然都是用CLUSTERED INDEX，但检索的效率却大相径庭。

--//测试脚本
USE Northwind
GO
if exists(select * from sysobjects where name=N''''stress_test'''' and type=''''U'''')
Drop table stress_test
GO
--//定义测试的表stress_test,存放所有的测试数据
Create table stress_test([id] int,[key] char(2))

GO
--//插入测试的数据
Set nocount on
--//变量定义
Declare @id int   --//Stress_test ID 值
Declare @key char(2)  --//Stress_test [key] 值
Declare @maxgroup int  --//组最大的循环数
Declare @maxLoop int  --//ID最大的循环数
Declare @tempGroup int  --//临时变量
Declare @tempLoop int  --//临时变量
Declare @tempint1 int  --//临时变量
Declare @tempint2 int  --//临时变量
Declare @rowcount int  --//记录事务提交的行数

--//初始化变量
Select @id=1
Select @maxgroup=1000
Select @maxLoop=1000
Select @tempGroup=1
Select @tempLoop=1
Select @key=''''''''
Select @rowcount=0

while @tempLoop<=@maxLoop
begin
 while @tempGroup<=@maxGroup
 begin
  select @tempint1=65+convert(int,rand()*50)
  select @tempint2=65+convert(int,rand()*100)
  if (@tempint1>=122 or @tempint2>=122)
    begin
    select @tempint1=@tempint1-100
    select @tempint2=@tempint2-100
   
    if (@tempint1<=65 or @tempint2<=65)
     begin
     select @tempint1=@tempint1+57
     select @tempint2=@tempint2+57
    end
   end
  select @key=char(@tempint1)+char(@tempint2)
  if @rowcount=0
  begin tran ins
     insert into stress_test([id],[key])values(@id,@key)
       select @rowcount=@rowcount+1
  
   if @rowcount>3000 --//判断当行数达到3000条时，开始提交事务
   begin
       commit tran ins
      select @rowcount=0
   end
  
  select @tempGroup=@tempgroup+1
 end
 if @rowcount>0
 begin
  commit tran ins
  select @rowcount=0
 end

 select @tempGroup=1
 select @id=@id+1
 select @tempLoop=@tempLoop+1
end
GO
--//删除KEY值为NULL的记录
delete stress_test where [key]is null
GO
--//建立簇索引PK_STRESS
Create Clustered index pk_stress on stress_test([Key])
--//建立非簇索引NI_STRESS_ID
Create NonClustered index NI_stress_id on stress_test([id])
GO
--//定义测试的表keytb
if exists(select * from sysobjects where name=N''''keytb'''' and type=''''U'''')
Drop table keytb
GO
create table keytb   -----//存放你需要匹配的值的表
(
  kf1  varchar(20)
)

--//存放你需要匹配的值，暂定为三个
insert into keytb(kf1) values(''''Az'''');
insert into keytb(kf1) values(''''Bw'''');
insert into keytb(kf1) values(''''Cv'''');

--insert into keytb(kf1) values(''''Du'''');
--insert into keytb(kf1) values(''''Ex'''');
--insert into keytb(kf1) values(''''Fy'''');
GO

下面我们就开始测试几种T-SQL的INDEX优化问题：

--先对1百万条/1亿条记录进行测试（选取3列）的T-SQL:

PRINT ''''第一种语句：''''
SET STATISTICS TIME  ON
SET STATISTICS IO  ON
select a.[id] from
(select distinct [id] from stress_test where [key] = ''''Az'''') a,
(select distinct [id] from stress_test where [key] = ''''Bw'''') b ,
(select distinct [id] from stress_test where [key] = ''''Cv'''') c
where a.id = b.id and a.id = c.id
GO
PRINT ''''第二种语句：''''
select [id]
from stress_test 
where [key]=''''Az'''' or [key]=''''Bw'''' or [key]=''''Cv''''
group by id having(count(distinct [key])=3)
GO
PRINT ''''第三种语句：''''
select distinct [id] from stress_test A where
not exists (
select 1 from
(select ''''Az'''' as k union all select ''''Bw'''' union all select ''''Cv'''') B
left join stress_test C on  C.id=A.id and B.[k]=C.[key]
where C.id is null)
GO
PRINT ''''第四种语句：''''
select distinct a.id from stress_test a
 where not exists
 ( select * from keytb c
   where not exists
   ( select * from stress_test b
     where
       b.id = a.id
       and
       c.kf1 = b.[key]
   )
 )
GO
PRINT ''''第五种语句：''''
SELECT distinct a.[id] FROM stress_test AS a,stress_test AS b,stress_test AS c
WHERE a.[key]=''''Ac'''' AND b.[key]=''''Bb'''' AND c.[key]=''''Ca''''
      AND a.[id]=b.[id] AND a.[id]=c.[id]

GO
SET STATISTICS TIME  OFF
SET STATISTICS IO  OFF

--先对1百万条/1亿条记录进行测试（选取6列）的T-SQL:
PRINT ''''第一种语句：''''
SET STATISTICS TIME  ON
SET STATISTICS IO  ON
select a.[id] from
(select distinct [id] from stress_test where [key] = ''''Az'''') a,
(select distinct [id] from stress_test where [key] = ''''Bw'''') b ,
(select distinct [id] from stress_test where [key] = ''''Cv'''') c,
(select distinct [id] from stress_test where [key] = ''''Du'''') d,
(select distinct [id] from stress_test where [key] = ''''Ex'''') e,
(select distinct [id] from stress_test where [key] = ''''Fy'''') f
where a.[id] = b.[id] and a.[id] = c.[id] and a.[id] = d.[id] and a.[id] = e.[id] and a.[id] = f.[id]
GO
PRINT ''''第二种语句：''''
select [id]
from stress_test 
where [key]=''''Az'''' or [key]=''''Bw'''' or [key]=''''Cv'''' or [Key]=''''Du''''or [Key]=''''Ex''''or [Key]=''''Fy''''
group by id having(count(distinct [key])=6)
GO
PRINT ''''第三种语句：''''
select distinct [id] from stress_test A where
not exists (
select 1 from
(select ''''Az'''' as k union all select ''''Bw'''' union all select ''''Cv''''union all select ''''Du''''union all select ''''Ex''''union all select ''''Fy'''') B
left join

上一页  [1] [2] [3]  下一页

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18