标题: MSSQL网站制作之MS SQL Server数据库查询优化及分页算法... [打印本页] 作者: 灵魂腐蚀 时间: 2015-1-16 22:22 标题: MSSQL网站制作之MS SQL Server数据库查询优化及分页算法... MySQL这个名字是怎么来的已经不清楚了。基本指南和大量的库和工具带有前缀“my”已经有10年以上，而且不管怎样，MySQLAB创始人之一的的女儿也叫My。server|分页|数据|数据库|算法|优化切磋怎样在有着1000万条数据的MSSQLSERVER数据库中完成疾速的数据提取和数据分页。以下代码申明了我们实例中数据库的“红头文件”一表的部分数据布局：
CREATETABLE[dbo].[TGongwen](--TGongwen是红头文件表名
[Gid][int]IDENTITY(1,1)NOTNULL,
--本表的id号，也是主键
[title][varchar](80)COLLATEChinese_PRC_CI_ASNULL,
--红头文件的题目
[fariqi][datetime]NULL,
--公布日期
[neibuYonghu][varchar](70)COLLATEChinese_PRC_CI_ASNULL,
--公布用户
[reader][varchar](900)COLLATEChinese_PRC_CI_ASNULL,
--必要扫瞄的用户。每一个用户两头用分开符“,”分隔
)ON[PRIMARY]TEXTIMAGE_ON[PRIMARY]
GO
上面，我们交往数据库中增加1000万条数据：
declare@iint
set@i=1
while@i<=250000
begin
insertintoTgongwen(fariqi,neibuyonghu,reader,title)values(2004-2-5,通讯科,通讯科,办公室,王局长,刘局长,张局长,admin,刑侦支队,特勤支队,交巡警支队,经侦支队,户政科,治安支队,外事科,这是开始的25万笔记录)
set@i=@i+1
end
GO
declare@iint
set@i=1
while@i<=250000
begin
insertintoTgongwen(fariqi,neibuyonghu,reader,title)values(2004-9-16,办公室,办公室,通讯科,王局长,刘局长,张局长,admin,刑侦支队,特勤支队,交巡警支队,经侦支队,户政科,外事科,这是两头的25万笔记录)
set@i=@i+1
end
GO
declare@hint
set@h=1
while@h<=100
begin
declare@iint
set@i=2002
while@i<=2003
begin
declare@jint
set@j=0
while@j<50
begin
declare@kint
set@k=0
while@k<50
begin
insertintoTgongwen(fariqi,neibuyonghu,reader,title)values(cast(@iasvarchar(4))+-8-153:+cast(@jasvarchar(2))+:+cast(@jasvarchar(2)),通讯科,办公室,通讯科,王局长,刘局长,张局长,admin,刑侦支队,特勤支队,交巡警支队,经侦支队,户政科,外事科,这是最初的50万笔记录)
set@k=@k+1
end
set@j=@j+1
end
set@i=@i+1
end
set@h=@h+1
end
GO
从上表中，我们能够看出，三种存储历程在实行100页以下的分页命令时，都是能够信托的，速率都很好。但第一种计划在实行分页1000页以上后，速率就降了上去。第二种计划约莫是在实行分页1万页以上后速率入手下手降了上去。而第三种计划却一直没有年夜的降势，潜力仍旧很足。
在断定了第三种分页计划后，我们能够据此写一个存储历程。人人晓得SQLSERVER的存储历程是事前编译好的SQL语句，它的实行效力要比经由过程WEB页面传来的SQL语句的实行效力要高。上面的存储历程不但含有分页计划，还会依据页面传来的参数来断定是不是举行数据总数统计。
--猎取指定页的数据
CREATEPROCEDUREpagination3
@tblNamevarchar(255),--表名
@strGetFieldsvarchar(1000)=*,--必要前往的列
@fldNamevarchar(255)=,--排序的字段名
@PageSizeint=10,--页尺寸
@PageIndexint=1,--页码
@doCountbit=0,--前往纪录总数,非0值则前往
@OrderTypebit=0,--设置排序范例,非0值则降序
@strWherevarchar(1500)=--查询前提(注重:不要加where)
AS
declare@strSQLvarchar(5000)--主语句
declare@strTmpvarchar(110)--一时变量
declare@strOrdervarchar(400)--排序范例
if@doCount!=0
begin
if@strWhere!=
set@strSQL="selectcount(*)asTotalfrom["+@tblName+"]where"+@strWhere
else
set@strSQL="selectcount(*)asTotalfrom["+@tblName+"]"
end
--以上代码的意义是假如@doCount传送过去的不是0，就实行总数统计。以下的一切代码都是@doCount为0的情形
else
begin
if@OrderType!=0
begin
set@strTmp="<(selectmin"
set@strOrder="orderby["+@fldName+"]desc"
--假如@OrderType不是0，就实行降序，这句很主要！
end
else
begin
set@strTmp=">(selectmax"
set@strOrder="orderby["+@fldName+"]asc"
end
if@PageIndex=1
begin
if@strWhere!=
set@strSQL="selecttop"+str(@PageSize)+""+@strGetFields+"from["+@tblName+"]where"+@strWhere+""+@strOrder
else
set@strSQL="selecttop"+str(@PageSize)+""+@strGetFields+"from["+@tblName+"]"+@strOrder
--假如是第一页就实行以上代码，如许会加速实行速率
end
else
begin
--以下代码付与了@strSQL以真正实行的SQL代码
set@strSQL="selecttop"+str(@PageSize)+""+@strGetFields+"from["
+@tblName+"]where["+@fldName+"]"+@strTmp+"(["+@fldName+"])from(selecttop"+str((@PageIndex-1)*@PageSize)+"["+@fldName+"]from["+@tblName+"]"+@strOrder+")astblTmp)"+@strOrder
if@strWhere!=
set@strSQL="selecttop"+str(@PageSize)+""+@strGetFields+"from["
+@tblName+"]where["+@fldName+"]"+@strTmp+"(["
+@fldName+"])from(selecttop"+str((@PageIndex-1)*@PageSize)+"["
+@fldName+"]from["+@tblName+"]where"+@strWhere+""
+@strOrder+")astblTmp)and"+@strWhere+""+@strOrder
end
end
exec(@strSQL)
GO