[转载]通用存储过程分页(使用ROW_NUMBER()和不使用ROW_NUMBER()两种情况)性能分析

[转载]通用存储过程分页(使用ROW_NUMBER()和不使用ROW_NUMBER()两种情况)性能分析 – Jiangrod Sky – 博客园.

表结构:

CREATE TABLE [dbo].[Xtest](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[XName] [nvarchar](50) NULL,
[XDemo] [nvarchar](500) NULL,
CONSTRAINT [PK_xtest] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

插入50万条数据:
declare @i int
set @i=1
while(@i<500001)
begin
insert into dbo.Xtest(XName,XDemo) values(CAST(@i as varchar)+’name’+CAST(@i as varchar),’这是第’+CAST(@i as varchar)+’的内容’)
set @i=@i+1
end

另外对XName建立索引。

存储过程如下:

代码


=============================================
Author:        <jiangrod>
Create date: <2010-03-03>
Description:    <SQL2000 通用分页存储过程>调用方 法: sp_Pager2000 ‘xtest’,’*’,’ID’,’ORDER BY ID ASC’,’xname like ”%222name%”’,3,20,0,0
=============================================
ALTER PROCEDURE [dbo].[sp_Pager2000]
@tblName varchar(255), 表名 如:’xtest’
@strGetFields varchar(1000) = *, 需要返回的列如:’xname,xdemo’
@pkName nvarchar(50)=ID, 主键名
@strOrder varchar(255)=, 排序的字段名如:’order by id desc’
@strWhere varchar(1500) = , 查询条件(注意:不要加where) 如:’xname like ”%222name%”’
@pageIndex int = 1, 页码如:2
@pageSize int = 20, 每页记录 数如:20
@recordCount int=0 out, 记录总数
@doCount bit=0 非0则统计,为0则不统计(统计会影响效率)
AS

Declare @SQL nvarchar(4000)
Declare @SQLtemp nvarchar(1000)
set @sql= From +@tblName
if(@strWhere!=) set @sql=@sql+ Where +@strWhere

declare @strCount nvarchar(1000)
总记录条数
if(@doCount!=0)
begin
if(@strWhere !=)
begin
set @strCount=set @num=(select count(1) from + @tblName + where +@strWhere+ )
end
else
begin
set @strCount=set @num=(select count(1) from + @tblName + )
end
EXECUTE sp_executesql @strCount ,N@num INT output,@RecordCount output
end

if @strWhere !=
begin
set @strWhere= where +@strWhere
end

if (@PageIndex>1)
begin
set @sqltemp=@PKName+ not in (Select Top +cast((@PageSize*(@PageIndex1)) as nvarchar)+ +@PKName+ +@sql
if(@strOrder!=)set @sqltemp=@sqltemp+ +@strOrder
set @sqltemp=@sqltemp+)
set @sql=Select Top +cast(@PageSize as nvarchar)+ +@strGetFields+ +@sql
if(@strWhere!=)set @sql=@sql+ And +@sqltemp
else set @sql=@sql+ Where +@sqltemp
end
else
begin
set @sql=Select Top +cast(@PageSize as nvarchar)+ +@strGetFields+ +@sql
end
if(@strOrder!=) set @sql=@sql+ +@strOrder
exec(@sql)

=============================================
Author:        <jiangrod>
Create date: <2010-03-03>
Description:    <SQL2005 及后续版本通用分页存储过程>调用方 法: sp_Pager2005 ‘xtest’,’*’,’ORDER BY ID ASC’,’xname like ”%222name%”’,2,20,0,0
=============================================
ALTER PROCEDURE [dbo].[sp_Pager2005]
@tblName varchar(255), 表名 如:’xtest’
@strGetFields varchar(1000) = *, 需要返回的列如:’xname,xdemo’
@strOrder varchar(255)=, 排序的字 段名如:’order by id desc’
@strWhere varchar(1500) = , 查询条件(注意:不要加 where)如:’xname like ”%222name%”’
@pageIndex int = 1, 页码如:2
@pageSize int = 20, 每页记录 数如:20
@recordCount int output, 记录总数
@doCount bit=0 非0则统计,为0则不统计(统计会影响效率)
AS

declare @strSQL varchar(5000)
declare @strCount nvarchar(1000)
总记录条数
if(@doCount!=0)
begin
if(@strWhere !=)
begin
set @strCount=set @num=(select count(1) from + @tblName + where +@strWhere+ )
end
else
begin
set @strCount=set @num=(select count(1) from + @tblName + )
end
EXECUTE sp_executesql @strCount ,N@num INT output,@RecordCount output
end

if @strWhere !=
begin
set @strWhere= where +@strWhere
end
set @strSQL=SELECT * FROM (SELECT ROW_NUMBER() OVER (+@strOrder+) AS ROWID,
set @strSQL=@strSQL+@strGetFields+ FROM [+@tblName+] +@strWhere
set @strSQL=@strSQL+) AS sp WHERE ROWID BETWEEN +str((@PageIndex1)*@PageSize+1)
set @strSQL=@strSQL+ AND +str(@PageIndex*@PageSize)
exec (@strSQL)

对上面两个存储过程进行了测试,测试环境:酷睿2双核2.20Gh,1G内存,SQL SERVER2008

对50万条数据进行了分页查询测试

查询的条件是:xname like ‘%222name%’

“page1:2秒937毫秒”:表示打开第一页使用的时间,其他依次类推

不使用ROW_NUMBER()每页都要统计:
page1:2秒937毫秒
page2:3秒140毫秒
page3:3秒 453毫秒
page4:3秒609毫秒
page5:3秒843毫秒
page6:4秒156毫秒

不使用ROW_NUMBER()仅第一页统计:
page1:2秒937毫秒
page2:0秒343毫秒
page3:0秒 593毫秒
page4:0秒812毫秒
page5:1秒46毫秒
page6:1秒281毫秒

使用ROW_NUMBER()每页都要统计:
page1:2秒937毫秒
page2:3秒31毫秒
page3:3秒156 毫秒
page4:3秒296毫秒
page5:3秒421毫秒
page6:3秒515毫秒

使用ROW_NUMBER()仅第一页统计:
page1:2秒937毫秒
page2:0秒218毫秒
page3:0秒 359毫秒
page4:0秒468毫秒
page5:0秒578毫秒
page6:0秒687毫秒

结论:在存储过程当中如果每次都统计记录总数将会严重影响效率,相同的查询条件记录总数必定是相同的,所以如果第一页执行之后把记录总数保存起来, 点击其他页次的时候命令存储过

程不再统计记录总数将会大大提高系统性能。通过测试结果看出每次都要统计总记录数使用ROW_NUMBER()和不使用ROW_NUMBER()差 别不是太大,如果仅第一次统计总记录数使用ROW_NUMBER()将

会比不使用ROW_NUMBER()的效率提高很多。

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏