SQLServer索引结构及其使用(三)
实现小数据和大数据的通用分页显示存储过程。
要构建一个Web应用,分页浏览功能是必不可少的。这个问题在数据库处理中很常见。经典的数据分页方法是:ADO记录集分页方法,即利用ADO自带的分页功能(使用光标)实现分页。但是这种分页方式只适用于数据量较小的情况,因为游标本身就有一个缺点:游标存储在内存中,很消耗内存。一旦创建了游标,相关记录就会被锁定,直到游标被取消。光标提供了一种逐行扫描特定集合的方法。一般用游标逐行遍历数据,根据取数据的不同条件进行不同的操作。但是对于多表和大表中定义的游标(大数据集)循环,程序很容易进入长时间等待甚至崩溃。
更重要的是,对于非常大的数据模型,按照传统的分页检索的方法,每次都要加载整个数据源,这是一种资源浪费。现在流行的分页方式一般是检索一个页面大小的块的数据,而不是检索所有数据,然后一步执行当前行。
按照页面大小和页码提取数据,最早也是最好的方法,大概就是“俄语存储过程”。此存储过程使用游标。由于游标的局限性,这种方法还没有得到普遍认可。
后来网上有人修改了这个存储过程。以下存储过程是结合我们的办公自动化示例编写的分页存储过程:
创建过程分页1
(@ pagesizeint,-页面大小,比如每页存储20条记录。
@ page index int-当前页码
)
如同
设置nocount on
开始
declare @ Indextable Table(id int identity(1,1),NID int)-定义表变量。
Declare @PageLowerBound int -定义这个页面的底层代码。
Declare @PageUpperBound int -定义这个页面的顶层代码。
set @ PageLowerBound =(@ pageindex-1)* @ pagesize
set @ PageUpperBound = @ PageLowerBound+@ pagesize
设置rowcount @PageUpperBound
insert into @indextable(nid)从TGongwen中选择gid
其中,法里基> dateadd(day,-365,getdate())由法里基·desc排序
从TGongwen O,@indextable t中选择O.gid,O.mid,O.title,O.fadanwei,O.fariqi
其中O.gid=t.nid和t.id>@PageLowerBound
和t.id
目标
Set count off
以上存储过程使用了SQL SERVER的最新技术——表变量。应该说这个存储过程也是一个优秀的分页存储过程。当然,在这个过程中,你也可以把表变量写成临时表:CREATE TABLE #Temp。但显然,在SQL SERVER中,使用临时表没有使用表变量快。所以刚开始用这个存储过程的时候,感觉非常好,速度比原来的ADO要好。但是后来,我发现了一个比这个更好的方法。
笔者曾经在网上看到一篇短文《从数据表中取出第N条到第M条记录的方法》。全文如下:
从发布表中取出文章N到M的记录:
选择m-n+1 *
从发布
我不在
(选择n-1个id
来自发布))
是id发布表的关键词
看到这篇文章的时候,真的觉得神清气爽,觉得这是一个非常好的想法。后来,当我在做一个办公自动化系统(ASP。NET+C #+SQL Server),突然想起了这篇文章。我认为如果修改这个语句,它可能是一个非常好的分页存储过程。所以我在网上搜了这篇文章。没想到,文章还没找到,我就发现了一个按照这个语句写的分页存储过程。这个存储过程也是目前比较流行的分页存储过程。很遗憾,我没有急于将这段文字转换成存储过程:
创建过程分页2
(
@ sqlnvarchar (4000),-不带排序语句的SQL语句
@ pageint,-页码
@ recsperpageint,-每页的记录数
@ idvarchar (255),-要排序的非重复ID号
@ sort varchar(255)-排序字段和规则
)
如同
声明@Str nVARCHAR(4000)
SET @ Str = ' ' SELECT ' '+CAST(@ RecsPerPage AS VARCHAR(20))+' ' * FROM
(' '+@SQL+'') T其中T.''+@ID+' ' '不在(SELECT ' '+CAST((@ RecsPerPage *(@ Page-1))
AS VARCHAR(20))+' ' ' '+@ ID+' ' FROM('+@ SQL+' ')T9 ORDER BY ' '+@ Sort+' ')ORDER BY ' '+@ Sort
打印@Str
EXEC sp_ExecuteSql @Str
GO
实际上,上述语句可以简化为:
选择页面尺寸*
来自表1,其中(id不在(从按id排序的表中选择页面大小*页面ID))
Order ID
但是这个存储过程有一个致命的缺陷,就是它包含了NOT IN这个词。虽然我可以把它转换成:
选择页面尺寸*
从不存在的表1
(select * from(select top * from table 1 order by id)b其中b.id = a.id)
Order id
也就是用not exists代替not in,但是我们之前已经讲过了,两者的执行效率其实没有区别。即便如此,使用这种组合NOT IN的方法也比使用cursor要快。
虽然使用not exists无法保存最后一个存储过程的效率,但是在SQL SERVER中使用关键字是非常明智的选择。因为分页优化的最终目的是避免过多的记录集,而上面我们已经提到了优点,我们可以通过它来控制数据量。
在分页算法中,影响我们查询速度的关键因素有两个:和不在。它可以提高我们的查询速度,但NOT IN会减慢我们的查询速度,所以要提高我们整个分页算法的速度,就必须彻底改造NOT IN,用其他方法代替。
我们知道,几乎在任何一个字段中,我们都可以通过max (field)或min (field)提取某个字段的最小值或最大值,所以如果这个字段没有重复,那么我们就可以用这些不重复字段的max或min作为分水岭,使之成为分页算法中分离每一页的参考。这里,我们可以使用运算符“>”或“200
,因此我们有以下分页方案:
选择页面尺寸*
来自表1
其中id >
(从中选择最大值(id)
(从表1中选择top((页码-1)*页面大小)order by id)作为T
)
Order id
在选择不重复值且易于区分大小的列时,我们通常选择主键。下表列出了作者在有1000万数据的办公自动化系统中使用的表,这些表是用GID(GID是主键,但不是聚簇索引)索引的。)对列进行排序,提取gid、fariqi、title字段,以第1页、第10页、第100页、第500页、第1000页、第100000页、第100000页、第250000页和第500000页为例,测试上述三种分页方案的执行速度:(单位:毫秒)
页码1方案2方案3 1 60 30 76 10 46 16 63 100 1076 720 130 540 12943 83 1000 17110 470 250 10000 24796 4500 140 100000 38326 42283 1553 250000 28140 12 820 2330 500000 1211然而,在第一种方案中,在分页超过1000页之后,速度下降。第二种方案是分页超过10000页后速度开始变慢。但第三方案一直没有大的下跌趋势,后劲还是很足的。
确定了第三种分页方案后,我们就可以根据它编写一个存储过程了。众所周知,SQL SERVER的存储过程是预先编译好的SQL语句,其执行效率比通过网页传输的SQL语句要高。下面的存储过程不仅包含分页方案,还根据来自页面的参数确定是否对总数据进行计数。
-获取指定页面的数据:
创建过程分页3
@ tblname varchar (255),-表名
@ strgetfields varchar (1000) ='' * ',-要返回的列
@ fldname varchar (255) = ' ' ',-排序的字段名称
@ pagesizeint = 10,-页面大小
@ pageindexint = 1,-页码
@ docount bit = 0,-返回记录总数,否则不为0则返回。
@ ordertypebit = 0,-设置排序类型,非零值按降序排列。
@ strhere varchar(1500)= ' ' '-查询条件(注意:不要加where)
如同
declare @ strsqlvarchar(5000)-主语句
declare @ strtmpvarchar(110)-临时变量
declare @ strordevarchar(400)-排序类型
if @doCount!= 0
开始
if @strWhere!=''''
set @ strSQL = " select count(*)as Total from["+@ TB lname+"]where "+@ str where
其他
set @ strSQL = " select count(*)as Total from["+@ TB lname+"]"
end
-上面的代码意味着如果@doCount传递非0的值,将执行总计数。以下所有代码都是@doCount为0的情况:
其他
开始
if @OrderType!= 0
开始
set @strTmp = "
set @ stroder = " order by["+@ fld name+"]desc "
-如果@OrderType不为0,则执行降序。这句话很重要!
目标
其他
开始
set @strTmp = " >(选择最大值"
set @ strOrder = " order by["+@ fld name+"]ASC "
目标
如果@PageIndex = 1
开始
if @strWhere!= ''''
set @ strSQL = " select top "+str(@ PageSize)+" "+@ strGetFields+"
from [" + @tblName + "]其中"+ @strWhere + " " + @strOrder
其他
set @ strSQL = " select top "+str(@ PageSize)+" "+@ strGetFields+"
from["+@ TB lname+"]"+@ stroder
-如果是第一页,执行上面的代码,会加快执行速度。
目标
其他
begin
-下面的代码为@strSQL提供了实际执行的SQL代码
set @ strSQL = " select top "+str(@ PageSize)+" "+@ strGetFields+" from["
+ @tblName + "]其中["+@ fld name+"]"+@ strTmp+"(["+@ fld name+"])
from(select top "+str((@ PageIndex-1)* @ PageSize)+"["+@ fld name+"]
from["+@ TB lname+"]"+@ strOrder+")as TBL tmp)"+@ strOrder
if @strWhere!= ''''
set @ strSQL = " select top "+str(@ PageSize)+" "+@ strGetFields+" from["
+ @tblName + "]其中[" + @fldName + "]" + @strTmp + "(["
+@ fldName+"])from(select top "+str((@ PageIndex-1)* @ PageSize)+"["
+@ fld name+"]from["+@ TB lname+"]where "+@ str where+" "
+ @strOrder +")作为tblTmp)和"+ @strWhere + " " + @strOrder
目标
目标
exec (@strSQL)
去
上面的存储过程是一般的存储过程,它的注释已经写在里面了。在数据量较大的情况下,尤其是查询最后几页时,查询时间一般不会超过9秒;但是使用其他存储过程在实践中会导致超时,所以这个存储过程非常适合查询大容量的数据库。希望通过以上存储过程的分析,能给大家一些启示,提高工作效率,也希望同行提出更好的实时数据分页算法。
0条评论