SQL Server - [SQL优化 TOP方面]
2006-09-11
TOP效率测试
先看一下TOP效率的两个测试。测试执行的SQL语句如下：
select top ? ItemNo,OrgCode from TblOrgItem order by ItemNo,OrgCode
TblOrgItem表的Clustered Index字段在这个查询中没有用到，有一个Non-Clustered Index在ItemNo上。TblOrgItem表的记录数为126000，ItemNo唯一的记录数为81000多，即ItemNo的索引密度（Index Density）相当高，同一个ItemNo值，最多对应14个OrgCode。做下面的测试前，已经确认ItemNo、OrgCode的统计信息（Statistics）均有维护，并进行过更新。下图为分别使用TOP 100、500、600、1000进行测试时，服务器端的Trace情况：
从图中可以看出，在TOP 500以下，速度很快，不用1秒钟完成查询（Duration<1秒）；TOP 600以后，虽然CPU和Reads有所增长，但Duration增长幅度更大，需要7秒甚至10来秒才完成查询。
从用户使用性角度看，响应时间超过5秒钟，总不是一件好事情，所以需要优化。优化方法之一，我们使用TOP 500以下来进行查询，这种情况下CPU和Reads都不算高，几百毫秒的时间也是完全可以接受。
500并不是一个确定的数字，有的表超过400就会很慢，需要根据实际情况进行测试才能知道具体的值是多少。一般的情况下，如果每页显示20条记录，界面默认显示15、20页数据，应当差不多。
另外一种方法，我们把ItemNo上的索引修改为(ItemNo,OrgCode)，上面的查询语句测试结果如下：
从上图可以看出，这种方式效率是最好的，TOP的记录数更多，而CPU和Reads却更低，速度更快。
测试总结
第一种测试情况，因为SELECT、ORDER BY子句中有字段不存在于所使用的索引中，这种情况下TOP指定的记录数比较小时，速度还是比较快，TOP指定的记录数超过一定程度，速度明显下降。
第二种测试情况，对索引进行修改，使查询所使用的列全都包含在索引中，也就是一个覆盖索引（Covering Index）。这种情况效率最高，但是存在索引字段方面的限制。
另外，上面的测试中，不管是第一种方式还是第二种方式，或者是TOP 100还是TOP 1000，Execution Plan都是一样的，即先对TblOrgItem表进行Index Scan，然后是TOP、ORDER的操作。对其它的表进行测试，结果也差不多。至于TOP 500和TOP 600时间为什么相差如此大，根据所有收集的信息，无法推测真正的原因。
优化中，完全的靠使用Covering Index提高效率，所受限制比较大，因为对于一个产品的各个功能，所要求的查询是各方各面的，索引的建立需要顾全产品整体。优化时可以尽可能的利用索引，将查询所涉及的数据范围尽可能的缩小在一个范围之内，再对其做其它的处理，构建最终所需的结果集。
实际优化示例
下面是根据上面的测试结果，对一个存储过程进行优化。先对存储过程的功能进行大致说明：
TblOrgItem是工厂物料表，TblPublicGroupItem是物料组的工厂物料清单。因为企业的工厂物料一般都比较多，很多和工厂物料相关的一些维护，工作量很大，因此将物料按照一定的规则条件进行分组（物料组），将符合规则的工厂物料添加到相应的物料组里面。这样，部分维护工作就可以在物料组上进行，在物料组上进行的维护，对这个物料组里所有的工厂物料有效。
存储过程是为某一个物料组添加工厂物料时，界面显示所需要的记录集。这个界面显示所有还没有被添加到这个物料组的工厂物料，供用户进行选择以确定是否将这些物料添加到这个物料组中。
@GroupID是物料组的ID，根据这个参数可以知道这个物料组中已经包含有哪些工厂物料。@OrgCode、@ItemNo是用户可能输入的查询条件。@PageIndex当前所请求的页码。@PageSize每页显示的记录数。@TopPage界面只显示最前面的页数（系统中的设定值是20）。
优化前的存储过程：
CREATE PROCEDURE ******
@GroupID as nvarchar(50),
@OrgCode as nvarchar(50),
@ItemNo as nvarchar(50),
@PageIndex as int,
@PageSize as int,
@TopPage as int
AS
create table #pubgroupitem_new(ID int identity(1,1),org nvarchar(40),item nvarchar(40))
declare @sql nvarchar(2000),
@count as int
set @sql=
insert into #pubgroupitem_new(org,item)
select top +cast(@TopPage*@PageSize as nvarchar(10))+oi.OrgID,oi.ItemID
from TblOrgItem oi
left join TblPublicGroupItem pgi on pgi.GroupOID=+@GroupID+ and pgi.OrgOID=oi.OrgID and pgi.ItemOID=oi.ItemID
where pgi.GroupOID is null
if @orgcode<>
set @sql=@sql+ and oi.orgcode like N%+@orgcode+%
if @ItemNo<>
set @sql=@sql+ and oi.ItemNo like N%+@ItemNo+%
set @sql=@sql+
order by oi.orgcode,oi.ItemNo
exec(@sql)
select a.org as OrgOID,o.orgcode,a.item as temOID,i.ItemNo,i.ItemName
from #pubgroupitem_new a
inner join TblItem i on a.item=i.OID
inner join TblOrgnization o on o.OID=a.org
where a.ID>=(@PageIndex-1)*@PageSize and a.ID<=@PageIndex*@PageSize
select (count(*)+@PageSize-1)/@PageSize as PageCount
from #pubgroupitem_new
drop table #pubgroupitem_new
--
GO
将ItemNo的Index修改成(ItemNo,OrgCode)，优化之后的存储过程：
CREATE PROCEDURE ******
@GroupID as nvarchar(50),
@orgcode as nvarchar(50),
@ItemNo as nvarchar(50),
@PageIndex as int,
@PageSize as int,
@TopPage as int
AS
create table #pubgroupitem_new(ID int identity(1,1),org nvarchar(40),item nvarchar(40))
create table #pubgroupitem_new_t(org nvarchar(40),item nvarchar(40),orgcode nvarchar(40),itemno nvarchar(40))
declare @sql nvarchar(2000),
@count as int,
@groupitemnum int
set @groupitemnum=isnull((select count(*) from TblPublicGroupItem where GroupOID=@GroupID),0)
set @sql=
insert into #pubgroupitem_new_t(orgcode,itemno)
select top +cast(@TopPage*@PageSize+@groupitemnum as nvarchar(10))+ OrgCode,ItemNo
from TblOrgItem
where 1=1
if @ItemNo<>
set @sql=@sql+ and ItemNo like N%+replace(@ItemNo,,)+%
if @orgcode<>
set @sql=@sql+ and OrgCode like N%+replace(@OrgCode,,)+%
set @sql=@sql+
order by ItemNo,OrgCode
exec(@sql)
update #pubgroupitem_new_t set org=o.OID
from TblOrgnization o with (nolock)
where o.OrgCode=#pubgroupitem_new_t.orgcode
update #pubgroupitem_new_t set item=i.OID
from TblItem i with (nolock)
where i.ItemNo=#pubgroupitem_new_t.itemno
delete #pubgroupitem_new_t
from TblPublicGroupItem pgi with (nolock)
where pgi.GroupOID=@GroupID and pgi.OrgOID=#pubgroupitem_new_t.org and pgi.ItemOID=#pubgroupitem_new_t.item
set @sql=@sql+
insert into #pubgroupitem_new(org,item)
select top +cast(@TopPage*@PageSize as nvarchar(10))+ org,item
from #pubgroupitem_new_t
exec(@sql)
select a.org as OrgOID,o.orgcode,a.item as temOID,i.ItemNo,i.ItemName
from #pubgroupitem_new a
inner join TblItem i on a.item=i.OID
inner join TblOrgnization o on o.OID=a.org
where a.ID>=(@PageIndex-1)*@PageSize and a.ID<=@PageIndex*@PageSize
order by i.ItemNo,o.orgcode
select (count(*)+@PageSize-1)/@PageSize as PageCount
from #pubgroupitem_new
drop table #pubgroupitem_new
drop table #pubgroupitem_new_t
yudi500
发表于 >2006-09-11 14:21:24 [评论 0] [浏览
0]