sql2000的分页存储过程在sql2005/2008中如何改进能大大减少执行时间？有难度，多谢！

好记忆不如烂笔头abc 2009-04-20 11:02:34
以下是sqlserver2000中经常用到分页存储过程

CREATE procedure p_splitpage
@sql nvarchar(4000),--要执行的sql语句
@page int=1, --要显示的页码
@pageSize int,--每页的大小
@pageCount int=0 out,--总页数
@recordCount int=0 out--总记录数
as
set nocount on
declare @p1 int
exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
set @recordCount = @pageCount
select @pagecount=ceiling(1.0*@pagecount/@pagesize),@page=(@page-1)*@pagesize+1
exec sp_cursorfetch @p1,16,@page,@pagesize
exec sp_cursorclose @p1
GO


问题是：在sqlserver2005或2008中，要求不改变原来存储过程中的参数，如何改写上面的存储过程能大大提高查询分页的执行效率？
如果能把原来的存储过程和改进后的存储过程在不同版本中花费的时间对比列出来就更好了。

解决立即给分！
...全文
540 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
limengailin 2011-07-09
  • 打赏
  • 举报
 回复
学习..........
flairsky 2009-04-22
  • 打赏
  • 举报
 回复 
Create procedure [dbo].[uspCustomPaging] 

    @TableName varchar(50),                  --表或视图名

    @Fields varchar(5000) = '*',              --字段名(全部字段为*)

    @OrderFields varchar(5000),            --排序字段(必须!支持多字段,建议建索引)

    @SqlWhere varchar(5000) = '',         --条件语句(如and Name='a')

    @PageSize int,                                     --每页多少条记录

    @PageIndex int = 1 ,                           --指定当前为第几页

    @TotalPages int output                    --返回总页数 

as

begin

    declare @sql nvarchar(4000)

    declare @TotalRecords int   



    --计算总记录数及总页数     

    set @sql = 'select @TotalRecords = count(*) from ' + @TableName + ' where 1=1 ' + @sqlWhere

    exec sp_executesql @sql,N'@totalRecords int output',@TotalRecords output

    select @TotalPages=CEILING((@TotalRecords+0.0)/@PageSize)



    --处理页数超出范围情况

    if @PageIndex<=0 

        set @PageIndex = 1

    if @PageIndex>@TotalPages

        set @PageIndex = @TotalPages



    set @sql = 'select '+ @Fields + ' from (select top(@PageIndex*@PageSize) ' + @Fields + ',row_number() over(order by ' + @OrderFields + ') as rowNumber from ' + @TableName + ' where 1=1 ' + @SqlWhere + ') t where t.rowNumber >= ((@PageIndex-1)*@PageSize+1)'

    

    --print @Sql   

    exec sp_executesql @sql,N'@PageIndex int, @PageSize int',@PageIndex,@PageSize  

end
让你望见影子的墙 2009-04-22
  • 打赏
  • 举报
 回复
如何改写上面的存储过程能大大提高查询分页的执行效率？


-->>还真以为2005和2008就是救世主啊。还是建立索引等等手段把
好记忆不如烂笔头abc 2009-04-22
  • 打赏
  • 举报
 回复
没有按要求写这个存储过程呀，高手们帮忙吧。
flairsky 2009-04-22
  • 打赏
  • 举报
 回复
总记录数count就是了，还要特意加上去？显示了总页数啊
好记忆不如烂笔头abc 2009-04-22
  • 打赏
  • 举报
 回复
[Quote=引用 8 楼 flairsky 的回复:]
SQL codeCreate procedure [dbo].[uspCustomPaging]
@TableName varchar(50), --表或视图名
@Fields varchar(5000) = '*', --字段名(全部字段为*)
@OrderFields varchar(5000), --排序字段(必须!支持多字段,建议建索引)
@SqlWhere varchar(5000) = '', --条件语句(如and Name='a')
@PageSize int, --每页多少条…
[/Quote]
总记录数没有啊
htl258_Tony 2009-04-20
  • 打赏
  • 举报
 回复
刚学习了一下SQL2005的，想改成row_number()的分页，发现效率不高，所以建议优化一下索引效率就不错了，原文如下：

体验Sql Server2005下Row_Number分页存储过程(2008-04-24 16:58:57)标签：it 分类：IT技术文章
如何使用Sql Server2005的Row_Number分页存储过程功能，就让我们来试试吧！

原来在sql server2000下所用的分页存储过程与使用Row_Number()编写的存储过程在Sql Server2005上的执行效率...

数据表：
REATE TABLE [dbo].[test](
[UserId] [int] Primary Key IDENTITY(1,1) ,
[UserName] [nvarchar](256) ,
[Sex] [varchar](50) NOT NULL,
[Age] [int] NOT NULL,
[Address] [varchar](100) ,
[status] [bit] NULL,
[Email] [varchar](100) ,
[InsertDate] [datetime] NOT NULL
)
插入1000k记录
use temp
Go

declare @n int
set @n = 0
while @n<1000000
BEGIN
Insert Into test(UserName,Sex,Age,Address,status,Email,InsertDate)
Values('bbisky','男','25','中国传媒大学现代远程教育中心',1,'denghaibo@live.com',getdate())
Select @n = @n+1
END

两个存储过程

原来使用Top的分页存储过程
Create proc [dbo].[test_PageById]
(
@pageIndex int,
@pageSize int
)
AS
SELECT TOP(@pageSize) *
FROM test
WHERE UserId <
(SELECT MIN(UserId) FROM (
SELECT TOP ((@pageIndex-1) * @pageSize) UserId
FROM test
ORDER BY UserId DESC)B )
ORDER BY UserId DESC

使用Row_number的存储过程
CREATE proc [dbo].[test_PageByRowNumber]
(
@pageIndex int,
@pageSize int
)
AS
DECLARE @startRow int, @endRow int
Set @startRow = (@pageIndex - 1) * @pageSize +1
SET @endRow = @startRow + @pageSize -1
SELECT*
FROM (
SELECT *,ROW_NUMBER() OVER (ORDER BY UserId DESC) AS RowNumber
FROM test ) T
WHERE T.RowNumber BETWEEN @startRow AND @endRow

测试和结果

SET STATISTICS io ON
SET STATISTICS time ON
go
EXEC test_PageByRowNumber 1000,50　--RowNumber存储过程
go
SET STATISTICS profile OFF
SET STATISTICS io OFF
SET STATISTICS time OFF


SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

(50 行受影响)
表 'test'。扫描计数 1，逻辑读取 50098 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

SQL Server 执行时间:
CPU 时间 = 219 毫秒，占用时间 = 213 毫秒。

SQL Server 执行时间:
CPU 时间 = 219 毫秒，占用时间 = 213 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

SET STATISTICS io ON
SET STATISTICS time ON
go
EXEC test_PageById 1000,50　--执行使用top语句的存储过程
go
SET STATISTICS profile OFF
SET STATISTICS io OFF
SET STATISTICS time OFF


SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

(50 行受影响)
表 'test'。扫描计数 2，逻辑读取 153 次，物理读取 0 次，预读 93 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

SQL Server 执行时间:
CPU 时间 = 16 毫秒，占用时间 = 26 毫秒。

SQL Server 执行时间:
CPU 时间 = 16 毫秒，占用时间 = 26 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。至此可以很直观的看出差距了.使用原来的Top子句的存储过程比使用Row_Number()的存储过程执行时间快了将近10倍.....

其实直接分析语句也可以看出，Row_Number()的效率不会是最高的，因为它必须先为100万条记条生成RowNumber,自然不会快到哪里去了.
不过前者的适应范围有些限制，即必须有一个为数字的唯一字段，如果使用uniqueidentifier为主键的话则不能使用了. Row_Number分页有很好的通用性和直观易用性，对于数据量较少来说，二者不会有很大的区别，具体是使用就根据我们的实际需要拉使用拉！ ⊙该文章转自[汕头电脑技术网[www.dnao168.cn] 原文链接：http://www.dnao168.cn/html/bianchengtiandi/shujuku/20080411/792.html
好记忆不如烂笔头abc 2009-04-20
  • 打赏
  • 举报
 回复
要求保留参数一致哦。
@sql nvarchar(4000),--要执行的sql语句
@page int=1, --要显示的页码
@pageSize int,--每页的大小
@pageCount int=0 out,--总页数
@recordCount int=0 out--总记录数

后面的怎么写？
htl258_Tony 2009-04-20
  • 打赏
  • 举报
 回复
特殊分页处理：
http://blog.csdn.net/zjcxc/archive/2004/12/22/225446.aspx
htl258_Tony 2009-04-20
  • 打赏
  • 举报
 回复
参考邹老大写的存储过程：
IF OBJECT_ID(N'dbo.p_show') IS NOT NULL

    DROP PROCEDURE dbo.p_show

GO



/**//*--实现分页的通用存储过程



    显示指定表、视图、查询结果的第X页

    对于表中主键或标识列的情况,直接从原表取数查询，其它情况使用临时表的方法

    如果视图或查询结果中有主键,不推荐此方法

    如果使用查询语句,而且查询语句使用了order by,则查询语句必须包含top 语句



最后更新时间: 2008.01.20

--邹建 2003.09(引用请保留此信息)--*/



/**//*--调用示例

EXEC dbo.p_show 

    @QueryStr = N'tb',

    @PageSize = 5,

    @PageCurrent = 3,

    @FdShow = 'id, colid, name',

    @FdOrder = 'colid, name'

select id, colid from tb

order by colid, name





EXEC dbo.p_show 

    @QueryStr = N'

SELECT TOP 100 PERCENT 

    * 

FROM dbo.sysobjects

ORDER BY xtype',

    @PageSize = 5,

    @PageCurrent = 2,

    @FdShow = 'name, xtype',

    @FdOrder = 'xtype, name'

--*/

CREATE PROC dbo.p_show

    @QueryStr nvarchar(4000),        -- 表名、视图名、查询语句

    @PageSize int=10,                -- 每页的大小(行数)

    @PageCurrent int=1,                -- 要显示的页

    @FdShow nvarchar (4000) = N'',    -- 要显示的字段列表,如果查询结果不需要标识字段,需要指定此值,且不包含标识字段

    @FdOrder nvarchar (1000) = N''    -- 排序字段列表

AS

SET NOCOUNT ON

DECLARE

    @FdName sysname,    --表中的主键或表、临时表中的标识列名

    @Id1 sysname,        --开始和结束的记录号

    @Id2 sysname,

    @Obj_ID int            --对象ID



--表中有复合主键的处理

DECLARE

    @strfd nvarchar(2000),        --复合主键列表

    @strjoin nvarchar(4000),    --连接字段

    @strwhere nvarchar(2000)    --查询条件





SELECT

    @Obj_ID = OBJECT_ID(@QueryStr),

    @FdShow = CASE 

                WHEN @FdShow > N'' THEN N' ' + @FdShow

                ELSE N' *'

            END,

    @FdOrder = CASE

                WHEN @FdOrder > N'' THEN N' ORDER BY ' + @FdOrder

                ELSE N' ' 

            END,

    @QueryStr = CASE

                WHEN @Obj_ID IS NULL THEN N' (' + @QueryStr + N')A'

                ELSE N' ' + @QueryStr

            END



-- 如果显示第一页，可以直接用 top 来完成

IF @PageCurrent = 1    

BEGIN

    SELECT 

        @Id1 = CAST(@PageSize as varchar(20))

    EXEC(N'

SELECT TOP ' + @Id1 + N'

    ' + @FdShow + N'

FROM ' + @QueryStr + N'

' + @FdOrder

)

    RETURN

END



-- 如果是表, 则检查表中是否有标识更或主键

IF @Obj_ID IS NULL OR OBJECTPROPERTY(@Obj_ID, 'IsTable') = 0

    GOTO lb_usetemp

ELSE

BEGIN

    SELECT

        @Id1 = CAST(@PageSize as varchar(20)),

        @Id2 = CAST((@PageCurrent - 1) * @PageSize as varchar(20))



    -- 标识列

    SELECT

        @FdName = name

    FROM dbo.syscolumns

    WHERE id = @Obj_ID

        AND status = 0x80

    IF @@ROWCOUNT = 0            --如果表中无标识列,则检查表中是否有主键

    BEGIN

        DECLARE

            @pk_number int



        SELECT

            @strfd = N'',

            @strjoin = N'',

            @strwhere = N''



        SELECT

            @strfd = @strfd 

                    + N',' + QUOTENAME(name),

            @strjoin = @strjoin 

                    + N' AND A.' + QUOTENAME(name) 

                    + N'=B.' +  QUOTENAME(name),

            @strwhere = @strwhere 

                    + N' AND B.' + QUOTENAME(name) + N' IS NULL'

        FROM(

            SELECT

                IX.id, IX.indid,

                IXC.colid, ixc.keyno,

                C.name

            FROM dbo.sysobjects O, 

                dbo.sysindexes IX,

                dbo.sysindexkeys IXC,

                dbo.syscolumns C

            WHERE O.parent_obj = @Obj_ID

                AND O.xtype = 'PK'

                AND O.name = IX.name

                AND IX.id = @Obj_ID

                AND IX.id = IXC.id

                AND IX.indid = IXC.indid

                AND IXC.id = C.id

                AND IXC.colid = C.colid

        )A

        ORDER BY keyno



        SELECT

            @pk_number = @@ROWCOUNT,            

            @strfd = STUFF(@strfd, 1, 1, N''),

            @strjoin = STUFF(@strjoin, 1, 5, N''),

            @strwhere = STUFF(@strwhere, 1, 5, N'')            



        IF @pk_number = 0

            GOTO lb_usetemp        --如果表中无主键,则用临时表处理

        ELSE IF @pk_number = 1

        BEGIN

            SELECT

                @FdName = @strfd

            GOTO lb_useidentity    -- 使用单一主键

        END

        ELSE

            GOTO lb_usepk        -- 使用复合主键

    END

END



/**//*--使用标识列或主键为单一字段的处理方法--*/

lb_useidentity:    

EXEC(N'

SELECT TOP ' + @Id1 + N'

    ' + @FdShow + N'

FROM '+@QueryStr + N'

WHERE ' + @FdName + ' NOT IN(

        SELECT TOP ' + @Id2 + N'

            ' + @FdName + '

        FROM ' + @QueryStr + N'

        ' + @FdOrder + N')

' + @FdOrder + N'

')

RETURN



/**//*--表中有复合主键的处理方法--*/

lb_usepk:        

EXEC(N'

SELECT 

    ' + @FdShow + N'

FROM(

    SELECT TOP ' + @Id1 + N'

        A.*

    FROM ' + @QueryStr + N' A

        LEFT JOIN(

                SELECT TOP ' + @Id2 + N'

                    ' + @strfd + N' 

                FROM ' + @QueryStr + N'

                ' + @FdOrder + N'

            )B

                ON ' + @strjoin + N'

    WHERE ' + @strwhere + N'

    ' + @FdOrder + N'

)A

' + @FdOrder + N'

')

RETURN



/**//*--用临时表处理的方法--*/

lb_usetemp:        

SELECT

    @FdName = QUOTENAME(N'ID_' + CAST(NEWID() as varchar(40))),

    @Id1 = CAST(@PageSize * (@PageCurrent-1) as varchar(20)),

    @Id2 = CAST(@PageSize * @PageCurrent-1 as varchar(20))



EXEC(N'

SELECT 

    ' + @FdName + N' = IDENTITY(int, 0, 1),

    ' + @FdShow + N'

INTO #tb

FROM(

    SELECT TOP 100 PERCENT 

        * 

    FROM ' + @QueryStr + N'

    ' + @FdOrder + N'

)A

' + @FdOrder + N'



SELECT 

    ' + @FdShow + N'

FROM #tb 

WHERE ' + @FdName + ' BETWEEN ' + @Id1 + ' AND ' + @Id2 + N'

'

)

GO
zhouxu_hust 2009-04-20
  • 打赏
  • 举报
 回复
学习
MySQL 存储过程（创建海量数据实验环境）
# 存储过程概念 存储过程是数据库管理中常用的技术之一，可以很方便的做些创建数据、统计数据、分析数据等工作，当前的主流数据库都支持存储过程，但不同的数据库环境语法结构有略微区别，本章给同学们介绍 MySQL 下...
字节跳动2024校招｜商业化&；商业产品与技术业务合集
事情起因是这样的，之前面试一家深圳企业，人家问我能接受多少税前工资，我说一个月2w，然后，然后就没有后续了所以问问大家，硕士学历被问到工资的话，给说多少比较合适。本硕双九非科班无实习cpper，面了几个一面都凉了，昨天美团一面，跟面试官扯了一个多小时，问的基本上都答上来了，手撕也过了，今天早上醒来睁眼一看进人才库了，心态。7.23高潜笔试，8.8邮件通知笔试未达标流程转提前批8.11一面，8.19二面，9.4HR面，9.8意向书感觉用友的面试难度不大，HR面时问了期望薪资，我说年。都是海量级别的简历。
编程资料 －C# 多线程

编程资料 － 多线程
C#多线程编程实例实战
作者： 刘弹 www.ASPCool.com 时间:2003-5-17 上午 10:24:05 阅读次数:10996
单个写入程序/多个阅读程序在.Net 类库中其实已经提供了实现，即
System.Threading.ReaderWriterLock 类。本文通过对常见的单个写入/多个阅读程序的分析来探索c#
的多线程编程。
问题的提出
所谓单个写入程序/多个阅读程序的线程同步问
高负载高并发网站架构分析
由于自己正在做一个高性能大用户量的论坛程序，对高性能高并发服务器架构比较感兴趣，于是在网上收集了不少这方面的资料和大家分享。希望能和大家交流 msn: defender_ios@hotmail.com ———————————————————————————————————————  初创网站与开源软件 6  谈谈大型高负载网站服务器的优化心得! 8  Lighttpd+S
[转]高负载并发网站架构分析
由于自己正在做一个高性能大用户量的论坛程序，对高性能高并发服务器架构比较感兴趣，于是在网上收集了不少这方面的资料和大家分享。希望能和大家交流 msn: defender_ios@hotmail.com ——————————————————————————————————————— ? 初创网站与开源软件 6 ? 谈谈大型高负载网站服务器的优化心得! 8 ? Lighttpd+Squid+Apach
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章