当前位置：首页 > 实用文档 > 其他范文> KMCT分页控件与存储过程分页完美结合存储过程分页篇

KMCT分页控件与存储过程分页完美结合存储过程分页篇

时间：2022-12-24 07:24:09 其他范文收藏本文下载本文

KMCT分页控件与存储过程分页完美结合存储过程分页篇（通用10篇）由网友“鲤鱼辅以葱姜蒜”投稿提供，以下是小编精心整理的KMCT分页控件与存储过程分页完美结合存储过程分页篇，希望对大家有所帮助。

篇1：KMCT分页控件与存储过程分页完美结合存储过程分页篇

上一篇分页控件(KCMT开源控件之--方便简洁的分页控件)出来以后，好几位网友期待我的存储过程分页与该控件结合使用的例程，这个星期工作很忙，一直没有时间完成，今天终于抽出时间来完成这篇文章。

首先从存储过程分页谈起

为什么要选择用存储过程分页呢？其实原因很简单，数据库查询功能的性能终究是有限的。即使我们对数据库进行了最优配置，对数据表设计再三斟酌，然而一旦面临海量数据，且返回结果集较大的时候，常规的查询语句就无能为力了。一般说来，当返回的结果集超过总数量的40%时，数据库层面上的优化就显得束手无策了。此时，我相信大多数同行首先想到的便是分页。当我们指定好每页的记录总数（PageSize）和当前页的索引（CurrentPage）时，理想的状况便发生了，首先我们不再从一个海量数据（百万级）中检索出超过40%的数据量，我们可以做个估算如果每页显示50条记录，那么也就是从100万条记录中查询50条记录，这个比例我相信大家都比较清楚。其次，网络中的数据通信量将大大缩减，我想这笔帐就不用我再做过多解释。同时，查询数量的减少对内存开销、页面的刷新、用户的等待时间都会得到相应的减少。

好处颇多，如何实现呢？我大致总结了以下几种实现方式。下面，我将一一介绍：

我将表分成两类

1.数据表中有唯一的自增索引，并且这个字段没有出现断号现象。在此我姑且称之为连续表，后面文章中出现的连续表就是指此类表。

2.数据表中不存在唯一的自增索引，或者存在唯一自增索引，但是由于删除记录等操作让该索引不连续，对于这类表我称之为不联系表。

分页之前我们模拟一张产品表，其结构如下图：

插入1000000条记录

DECLARE @I INTSET @I=0WHILE(@I<1000000)BEGININSERT INTO PRODUCT(ProductName,ProductAddDate) VALUES('产品名',GETDATE)SET @I=@I+1END

同时给出存储过程的结构，由于今天只讨论分页，所以查询条件、排序分组方法等请读者自行补充。

CREATE PROCEDURE SelectProduct @PageSize int, @CurrentPage int, @TotalPage int outputASBEGIN --select methodENDGO

说明：由于上一篇文章的分页控件需要一个总页数的参数，因此将@TotalPage 作为输出参数返回符合记录的总页数。@CurrentPage为0表示

第一页。

执行存储过程代码

SET STATISTICS PROFILE ON SET STATISTICS IO ON SET STATISTICS TIME ON GODECLARE @return_value int, @TotalPage intEXEC @return_value = [dbo].[SelectProduct] @PageSize = 50, @CurrentPage = 1, @TotalPage = @TotalPage OUTPUTSELECT @TotalPage as N'@TotalPage'SELECT 'Return Value' = @return_valueGOSET STATISTICS PROFILE OFF SET STATISTICS IO OFF SET STATISTICS TIME OFF

测试环境:

Win、SqlServer、720775条记录、每页50条记录、本机直接访问数据库、每组10次查询取平均值

连续表的分页方案：

方案：利用ID筛选出要得到的数据

CREATE PROCEDURE SelectProduct @PageSize int, @CurrentPage int, @TotalPage int outputASBEGIN EXEC('SELECT TOP '+@PageSize+'* FROM Product WHERE ProductId>('+@PageSize+'*'+@CurrentPage+')') SELECT @TotalPage=COUNT(*)/@PageSize FROM ProductEND

说明：SELECT TOP 后面不能直接跟变量，所以采用了拼接sql的办法

测试结果：

页码执行时间（ms） 11100 2100045000910000 1314000 16

分析结果发现在百万级数据都在小于0.1秒，这足以满足大多数要求，但是随着数据页的增大呈现一种查询变缓的趋势。

当然还有其他对ID进行比较的如：between and and so，当然还有游标分页，虽然通用性很好，但是性能很差。今天我就不一一列举，因

为今天要讨论的重点是不连续表的分页技术。

不连续表的分页技术

为了让上面的表不连续我们将部分记录删除

DELETE FROM Product WHERE ProductId%24=0

执行完成后(30032 行受影响)即30032条记录被删除，如果我们再用连续表的分页方式在此表上分页就不再适用。因为检索的记录中存在断

号现象。所以我们需寻求新的方法分页

方案一：重建数据表的唯一自增索引

DBCC CHECKIDENT (Product, RESEED,1)

重建之后采用连续表的分页方式，因为该方式是效率最高的分页查询，

该方案不适用于将该唯一自增索引作为其他表外键的关系型数据库，

这样将会导致数据混乱，望慎用。

方案二：采用临时表分页

局部临时表的生存期一次会话过程，说得简单点就是当一个用户执行一个查询时创建，查询执行完成后自动删除。

CREATE PROCEDURE [dbo].[SelectProduct] @PageSize int, @CurrentPage int, @TotalPage int outputASBEGIN DECLARE @BeginID INT ,@EndID INT SET @BeginId=@PageSize*@CurrentPage SET @EndID=@PageSize*(@CurrentPage+1) CREATE TABLE #TmpProduct( Id int IDENTITY(1,1) PRIMARY KEY, ProductId int not null) INSERT INTO #TmpProduct(ProductId) SELECT ProductId FROM Product SET @TotalPage=@@ROWCOUNT/@PageSize SELECT * FROM Product as p,#TmpProduct as t WHERE p.ProductId=t.ProductId and t.ProductId BETWEEN @BeginId AND @EndIDEND

测试结果：

记录总数查询时间(ms)10000 198100000 669250000 1454500000 3980700000 5543

由此我们发现规律，当数据量越小查询速度也就越快，因此该方法适用于小数据量的表。从执行计划中发现向临时表中插入数据占用了整个

查询过程的90%-95%时间，而真正查询我们想要的产品记录仅仅占了5%-10%，那么有没有办法不去反复执行插入过程呢？那么我们可以采用

全局临时表或者普通表，代码如下：

首先创建全局临时表并插入记录：

CREATE TABLE ##TmpProduct( Id int IDENTITY(1,1) PRIMARY KEY, ProductId int not null) INSERT INTO ##TmpProduct(ProductId) SELECT ProductId FROM Product

重写存储过程

CREATE PROCEDURE [dbo].[SelectProduct] @PageSize int, @CurrentPage int, @TotalPage int outputASBEGIN DECLARE @BeginID INT ,@EndID INT SET @BeginId=@PageSize*@CurrentPage SET @EndID=@PageSize*(@CurrentPage+1) SELECT * FROM Product as p,##TmpProduct as t WHERE p.ProductId=t.ProductId and t.ProductId BETWEEN @BeginId AND @EndID SELECT @TotalPage=COUNT(*)/@PageSize FROM PRODUCTEND

测试结果：

记录总数查询时间(ms)10000 1010000027250000415000006970000086

综合两种临时表分页方法分析，很明显采用全局临时表分页的效率远高于局部临时表分页，但是全局临时表需要定时维护，包括记录改变，

索引改变。这种维护成本限制了该方法的发展。所以，在小数据量的情况下建议使用局部临时表分页，如果数据量较大请参考下面的方案。

方案三：采用ROW_NUMBER()分页

CREATE PROCEDURE [dbo].[SelectProduct] @PageSize int, @CurrentPage int, @TotalPage int outputASBEGIN DECLARE @BeginID INT ,@EndID INT SET @BeginId=@PageSize*@CurrentPage SET @EndID=@PageSize*(@CurrentPage+1) SELECT * FROM (SELECT *,ROW_NUMBER()OVER(order by ProductId) AS ROWNUM FROM Product) as t WHERE ROWNUM BETWEEN @BeginId AND @EndID SELECT @TotalPage=COUNT(*)/@PageSize FROM Product END

测试结果：

记录总数查询时间(ms)10000210100000 150250000 165500000 69700000 86

从统计数据中不难看出

综合所有分页我们发现，分页无非就是对记录的编号进行处理，如果编号符合我们要求的我们就可以用连续表的方式直接使用，如果不符合

要求的，我们便改变这种编号使其符合要求，如重新建立编号，其实临时表和ROW_NUMBER()均属于重建编号的过程。

备注：文章中的数据均亲自实测得来，该数据仅作参考和比较，不同的目标机运行时间都将不同。如果文章中存在不正确的观点和看法，望

大家指出，不能误导读者。请大家尊重笔者的劳动果实，望注明出处： www.cnblogs.com/4inwork。当明白了存储过程分页的原

理子后下篇文章将结合控件来一个具体事例。

篇2：存储过程实现分页

USE [HDIS]

/****** Object: StoredProcedure [dbo].[AspNetPager] Script. Date: 12/30/ 09:00:35 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

Create procedure [dbo].[AspNetPager]

(@tablename nvarchar (1000), --表名

@filedname nvarchar (4000), --查询的字段

@startIndex int, --起始记录数

@endIndex int, --结束记录数

@where nvarchar (4000), --条件 (不包含where)

@orderfiled nvarchar (100), --排序字段（CreateDate desc）

@PageSize int,

@prmkeyName nvarchar (100),

@pageIndex int,

@docount bit)

begin

declare @date varchar(50),@sql nvarchar (4000) ,@i int

select @date =CONVERT(nvarchar(50), serverproperty(‘productversion‘))

--if(CONVERT(int, SUBSTRING(@date,0,3))>8) ------sql以上

-- begin

-- if(@docount=1)

-- set @sql = ‘select count(*) from ‘ + @tablename +‘ where ‘ + @where

-- else

-- begin

-- set @sql =‘

-- with temptbl as (

-- SELECT ROW_NUMBER() OVER (ORDER BY ‘+ @orderfiled +‘ )AS Row, * from ‘+ @tablename +‘ where ‘+ @where +‘)

-- SELECT ‘+ @filedname +‘ FROM temptbl where Row between ‘+CONVERT(nvarchar(100),@startIndex) +‘ and ‘+CONVERT(nvarchar(100),@endIndex )

-- END

-- exec (@sql)

-- end

--else

begin -------sql2000

if(@docount=1)

set @sql = ‘select count(*) from ‘ + @tablename +‘ where ‘ + @where

else

begin

set @i= CONVERT(nvarchar(100),@PageSize)*(CONVERT(nvarchar(100),@pageIndex)-1)

set @sql = ‘SELECT TOP ‘+ CONVERT(nvarchar(100),@PageSize) +‘ *

FROM ‘ + @tablename +‘ WHERE (‘+@where +‘ and‘+@prmkeyName+‘ NOT IN

(SELECT TOP ‘+CONVERT(nvarchar(100),@i)+‘ ‘ +@prmkeyName +‘

FROM ‘ + @tablename +‘ WHERE ‘ + @where+‘ ORDER BY ‘+ @orderfiled +‘)) ORDER BY ‘+ @orderfiled

end

--print(@sql)

exec (@sql)

end

篇3：分页存储过程代码

-10-10sql with as用法详解

2013-01-01sqlserver中关于WINDOWS性能计数器的介绍

2013-06-06解析sql中得到刚刚插入的数据的id

2014-06-06SQL Server出现System.OutOfMemoryException异常的解决方法

-03-03将Session值储存于SQL Server中

2013-10-10利用SQL语句给字段加注释的方法

2013-02-02SQL Server利用bcp命令把SQL语句结果生成文本文件

2014-03-03sql时间格式化输出、Convert函数应用示例

-06-06三步堵死 SQL Server注入漏洞

2009-08-08一个简单的SQL 行列转换语句

篇4：分页存储过程代码

热点排行

SQL Server 图文安装教程

SQL Server 安装图解教程(附

sqlserver中distinct的用法(不重

SQL Server导入、导出、备份数据

SQL语句去掉重复记录，获取重复记

SQL Server数据库入门学习总结

SQL Server错误代码大全及解释（

sql convert函数使用小结

sql 时间函数整理的比较全了

用SQL语句添加删除修改字段、一些

篇5：一个分页存储过程代码

-12-12一个常用的报表统计SQL语句

-04-04SQL Server 数据库转 SQL Server 的方法小结

-03-03搜索sql语句

2007-03-03海量数据库的查询优化及分页算法方案

-09-09一个删选数据的例子,使用GROUP、DISTINCT实例解析

2008-10-10在 SQLSERVER 中快速有条件删除海量数据

2010-08-08ADO.NET EF中的实体修改方法

2013-11-11sql server获得新记录标识列值的二种方法

2013-06-06浅析被遗忘的SQLServer比较运算符修饰词

2012-06-06ROW_NUMBER SQL Server 2005的LIMIT功能实现(ROW_NUMBER()排序函

篇6：一个分页存储过程代码

热点排行

SQL Server 图文安装教程

SQL Server 安装图解教程(附

sqlserver中distinct的用法(不重

SQL Server导入、导出、备份数据

SQL语句去掉重复记录，获取重复记

SQL Server数据库入门学习总结

SQL Server错误代码大全及解释（

sql convert函数使用小结

sql 时间函数整理的比较全了

用SQL语句添加删除修改字段、一些

篇7：关于存储过程分页，我的测试情况

在精华区中关于存储过程分页大概有三种思路

分别是：

临时表，参见658037 在下提供一个存储过程分页的方法

游标，参见658066 一种理论上最快的Web数据库分页方法！

set rowcount 方法，参见

658606 关于存储过程分页

我做了一个10万条记录的表来进行测试

结果临时表光建立数据就用了四十秒

游标有大小限制，我的机子上最大只能到1万条，更多的就无法用了，

关于存储过程分页，我的测试情况

，

set rowcount 方法不错，5000多页，只用不到1秒。

呵呵

篇8：一个高效的分页存储过程

一个高效的分页存储过程

最近在做一个几百万条数据的分页查询，研究了各种方案，在本机上用项目的实际数据库做测试，测试过程 is very 痛苦，不堪回首ing，现在废话不多说，直接上结果，相信这也是大多数搜索答案的人最愿意看的方式。

以下是存储过程的代码：

1 CREATE PROCEDURE [dbo].[P_GridViewPager] (

2 @recordTotal INT OUTPUT, --输出记录总数

3 @viewName VARCHAR(800), --表名

4 @fieldName VARCHAR(800) = '*', --查询字段

5 @keyName VARCHAR(200) = 'Id', --索引字段

6 @pageSize INT = 20, --每页记录数

7 @pageNo INT =1, --当前页

8 @orderString VARCHAR(200), --排序条件

9 @whereString VARCHAR(800) = '1=1' --WHERE条件

10 )

11 AS

12 BEGIN

13 DECLARE @beginRow INT

14 DECLARE @endRow INT

15 DECLARE @tempLimit VARCHAR(200)

16 DECLARE @tempCount NVARCHAR(1000)

17 DECLARE @tempMain VARCHAR(1000)

18 --declare @timediff datetime

20 set nocount on

21 --select @timediff=getdate --记录时间

23 SET @beginRow = (@pageNo - 1) * @pageSize + 1

24 SET @endRow = @pageNo * @pageSize

25 SET @tempLimit = 'rows BETWEEN ' + CAST(@beginRow AS VARCHAR) +' AND '+CAST(@endRow AS VARCHAR)

27 --输出参数为总记录数

28 SET @tempCount = 'SELECT @recordTotal = COUNT(*) FROM (SELECT '+@keyName+' FROM '+@viewName+' WHERE '+@whereString+') AS my_temp'

29 EXECUTE sp_executesql @tempCount,N'@recordTotal INT OUTPUT',@recordTotal OUTPUT

31 --主查询返回结果集

32 SET @tempMain = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (order by '+@orderString+') AS rows ,'+@fieldName+' FROM '+@viewName+' WHERE '+@whereString+') AS main_temp WHERE '+@tempLimit

34 --PRINT @tempMain

35 EXECUTE (@tempMain)

36 --select datediff(ms,@timediff,getdate()) as 耗时

38 set nocount off

39 END

41 GO

完工！

篇9：分页实现方法的性能比较存储过程

我们先给出几种主要的分页方法和核心语句,然后直接给出结论,有兴趣的读者可以看看后面的数据

几种常用存储过程分页方法

TopN方法

select Top(@PageSize) from TableName where ID Not IN

(Select Top ((@PageIndex-1)*@PageSize) ID from Table Name where .... order by ... )

where .... order by ...

临时表

declare @indextable table(id int identity(1,1),nid int,PostUserName nvarchar(50))

declare @PageLowerBound int

declare @PageUpperBound int

set @PageLowerBound=(@pageindex-1)*@pagesize--下限

set @PageUpperBound=@PageLowerBound+@pagesize--上限

set rowcount @PageUpperBound

insert into @indextable(nid,PostUserName) select ReplyID,PostUserName from TableName order by ......

select * from TableName p,@indextable t where p.ID=t.nid

and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id

CTE--新语法,类似临时表,但是生命周期稍微不同,这里只是他的一个运用

withcte_temp--定义零时表,PageIndex是一个计算字段,储存了搜索结果的页号

As (ceiling((Row_Number() over(order by.... )-1)/@pagesize as int) as PageIndex,* from TableName where.....)

select * fromcte_temp where pageindex=@pageindex-1;

结论:

TopN在小页数下最快,如果在10页以下,可以考虑用它,CTE和临时表时间很稳定,CTE消耗的时间比临时表多,但是不会引起tempdb的暴涨和IO增加

性能比较

试验环境:winserver,Sqlserver2005,库大小2,567,245行,没有where子句,试验时每页大小50,页码作为变量

取0,3,10,31,100,316,1000,3162...页,也就是10的指数,试验结果如下

页数TopNCTE临时表临时表老论坛存储过程CTE改进1312101014577302315779552446471911012755048838014646116325889672122360197676021004680973816642354867151316452719764323386752272551000无法计算9806869257863589483162无法计算98222485411012460821010000无法计算975478121192614250735931623无法计算97751872933218152497511100000无法计算无法计算3153855569171396124

数据解释和分析

临时表分为有没有缓存两种时间,CTE就是上面的方法,CTE改进只是把选入CTE临时表的列数减少了,只选取了页号和主键,Null表示时间无法计算(时间太长),数据单位是毫秒.

从上面的数据可

关键字：MYSQL

篇10：通用分页存储过程真的有注入漏洞吗

今天看了两篇关于存储过程SQL注入漏洞的文章:

1):如此高效通用的分页存储过程是带有sql注入漏洞的

2):防SQL注入：生成参数化的通用分页查询语句

怎么看怎么觉的别扭,在我印象中存储过程是不会存在注入漏洞的啊?起码我目前的水平还不了解如何注入存储过程，如果大家有注入的方法请指教。换句话说存储过程本身并无注入漏洞，只不过有漏洞大多都是因为程序漏洞导致。

我们来简化下之前两位园友讨论的分页存储过程,原代码太长,我这里呢写一个针对一个单表查询的存储过程。创建一个用户表,表结构如下:有三个字段，人员ID，姓名字段。

CREATE TABLE [dbo].[person](

[id] [int] NULL,

[last_name] [varchar](30) COLLATE Chinese_PRC_CI_AS NULL,

[first_name] [varchar](30) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]

然后写一个查询存储过程（getPerson）:作用，根据不同的条件读取用户信息。

IF ( EXISTS ( SELECT *

FROM sysobjects

WHERE id = OBJECT_ID(N'[dbo].[getPerson]')

AND OBJECTPROPERTY(id, N'IsProcedure') = 1 ) )

BEGIN

DROP PROCEDURE [dbo]. [getPerson]

END

Go

CREATE PROC getPerson

@strWhere VARCHAR(100) = '' -- 查询条件 (注意: 不要加 where)

AS

BEGIN

DECLARE @strSQL VARCHAR(1000) -- 主语句

SET @strSQL = 'select top 10 * from person where 1=1 '

--如果存在条件,则加上

IF @strWhere != ''

BEGIN

SET @strSQL = @strSQL + @strWhere

END

PRINT ( @strSQL )

EXEC ( @strSQL

)

END

查询方式，根据用户的姓来查询。要想最终的存储过程执行语法正确，同时不存在注入漏洞，此时条件的正确格式是:and first_name like '%Jim''s dog%'。

我们可以看到条件Jim's dog组装成SQL后，中间的单引号一定要变成两个。为了避免注入，我一般这样处理SQL拼接的安全问题:在C#写程序的时候应该这样写:

///
/// 屏蔽字符串中的特殊字符
/// by minjiang 07-07-06
///
public string SafeRequest (string str)
{
//定义要返回的字符串
string sReturn;
//将要处理的字符串转换为小写字母
str = str.ToLower ();
//定义特殊字符串
string SQL_KILL = “'|and|exec|insert|select|delete|update|count|*|%
|chr|mid|master|truncate|char|declare|set|;|from|=|--|drop|<|>”;
char[] separator ={ '|' };
string[] sql = SQL_KILL.Split(separator);

for(int i=0;i

{

//如果有特殊字符则将它替换成为空

if(str.IndexOf (sql [i].ToString ().ToLower ())>-1)

{

//把单引号替换成双引号

if (sql[i].ToString() == ”'“)

{ str = str.Replace(”'“, ”''“); }

else

{

//把敏感字符替换成空

str = str.Replace(sql[i].ToString().ToLower(), ”“);