大数据量分页存储过程效率测试附代码

9/1/2015来源:SQL技巧人气:1545

大数据量分页存储过程效率测试附代码

在项目中,我们经常遇到或用到分页,那么在大数据量(百万级以上)下,哪种分页算法效率最优呢?我们不妨用事实说话。

测试环境

硬件:CPU 酷睿双核T5750 内存:2G

软件:Windows server 2003 + Sql server 2005

OK,我们首先创建一数据库:data_Test,并在此数据库中创建一表:tb_TestTable

按 Ctrl+C 复制代码按 Ctrl+C 复制代码

然后我们在数据表中插入2000000条数据:

复制代码1--插入数据2setidentity_inserttb_TestTableon3declare@countint4set@count=15while@count<=20000006begin7insertintotb_TestTable(id,userName,userPWD,userEmail)values(@count,'admin','admin888','lli0077@yahoo.com.cn')8set@count=@count+19end10setidentity_inserttb_TestTableoff复制代码

我首先写了五个常用存储过程:

1,利用select top 和select not in进行分页,具体代码如下:

复制代码1createprocedureproc_paged_with_notin--利用selecttopandselectnotin2(3@pageIndexint,--页索引4@pageSizeint--每页记录数5)6as7begin8setnocounton;9declare@timediffdatetime--耗时10declare@sqlnvarchar(500)11select@timediff=Getdate()12set@sql='selecttop'+str(@pageSize)+'*fromtb_TestTablewhere(IDnotin(selecttop'+str(@pageSize*@pageIndex)+'idfromtb_TestTableorderbyIDASC))orderbyID'13execute(@sql)--因selecttop后不支技直接接参数,所以写成了字符串@sql14selectdatediff(ms,@timediff,GetDate())as耗时15setnocountoff;16end复制代码

2,利用select top 和 select max(列键)

复制代码1createprocedureproc_paged_with_selectMax--利用selecttopandselectmax(列)2(3@pageIndexint,--页索引4@pageSizeint--页记录数5)6as7begin8setnocounton;9declare@timediffdatetime10declare@sqlnvarchar(500)11select@timediff=Getdate()12set@sql='selecttop'+str(@pageSize)+'*Fromtb_TestTablewhere(ID>(selectmax(id)From(selecttop'+str(@pageSize*@pageIndex)+'idFromtb_TestTableorderbyID)asTempTable))orderbyID'13execute(@sql)14selectdatediff(ms,@timediff,GetDate())as耗时15setnocountoff;16end复制代码

3,利用select top和中间变量--此方法因网上有人说效果最佳,所以贴出来一同测试

复制代码1createprocedureproc_paged_with_Midvar--利用ID>最大ID值和中间变量2(3@pageIndexint,4@pageSizeint5)6as7declare@countint8declare@IDint9declare@timediffdatetime10declare@sqlnvarchar(500)11begin12setnocounton;13select@count=0,@ID=0,@timediff=getdate()14select@count=@count+1,@ID=casewhen@count<=@pageSize*@pageIndexthenIDelse@IDendfromtb_testTableorderbyid15set@sql='selecttop'+str(@pageSize)+'*fromtb_testTablewhereID>'+str(@ID)16execute(@sql)17selectdatediff(ms,@timediff,getdate())as耗时18setnocountoff;19