SQL Server的通用分页存储过程 未使用游标，速度更快！

正常情况下，SQL Server服务器上会对使用频率大的Table建立合适的索引
这样能大幅度的提高数据库本身的数据检索速度，建立索引的方法就不细说了

如果需要返回大量数据，从几百行到几万行，甚至几十万行数据
这时会发现响应速度越来越慢，甚至发生响应超时的错误
为了解决这种大数据量请求的问题，就不得不使用分页模式了

在这方面，JDBC就强悍得多，它可以将指定的行数和SQL请求一并发送给SQL Server，这样只返回分页后的数据，JDBC的原理还不清楚，但在实际使用中，速度还是非常快的

如果没办法使用JDBC，最常用的方法就是存储过程了！

我在写这个分页存储之前，参考了网上的大量相关文章，可以通过关键字：SQL Server 分页 进行搜索
他们主要都是利用SQL中的Top方法，并且对所检索的数据结构要求有标识列，如果没有标识列，或者是联合主键，那么就会非常麻烦了。而且对应用里原有的SQL检索部分需要修改的地方较多，工作量较大。

因此，我在写这个存储之前就要求一定要对原有的SQL脚本最大程度的兼容

经过一个下午的时间，和我一个同事（绝对是高手）的共同努力下，摸索出了以下的思路：

1、确定存储的输入参数：
1）SQL脚本，该参数接收完整的、正确的SQL检索文本，可将原应用中写好的SQL脚本直接传入
2）每页的数据容量，就是一页有多少条数据
3）当前页码
2、确定分页机制：
1）执行传入的SQL脚本，并将结果生成临时表
2）修改临时表的结构，增加标识列字段
3）根据标识列字段，计算出指定页码内的记录范围，并返回
4）返回总数据条数，用于客户端进行分页显示

根据以上的思路，编写出以下通用的分页存储过程：


代码如下:

[code]
--// ============================
--// SQL Server通用分页存储过程
--// Author : netwild
--// date : 2010/07/22
--// Email : netwild@163.com
--// QQ : 52100641（网无忌）
--// ============================

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROC execByPage

@sqlQuery varchar(2000), --//输入参数：SQL检索语句或表名
@pageSize int, --//输入参数：每页显示记录条数
@pageIndex int --//输入参数：当前页码

AS

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

declare @tmpTableName varchar(50)
set @tmpTableName = '##TB1516_' + replace(cast(newid() as varchar(40)),'-','') --//生成随机临时表名称

declare @subIndex int
set @subIndex = charindex('from',@sqlQuery)
if (@subIndex > 0)
begin --//带FROM的标准检索语句
declare @sqlQuery1 varchar(2000)
declare @sqlQuery2 varchar(2000)
set @sqlQuery1 = substring(@sqlQuery,1,@subIndex - 1)
set @sqlQuery2 = substring(@sqlQuery,@subIndex,len(@sqlQuery))
set @sqlQuery = @sqlQuery1 + ',IDENTITY(numeric,1,1) as ID1516 into ' + @tmpTableName + ' ' + @sqlQuery2
end
else --//不带FROM的表名
begin
set @sqlQuery = 'select *,IDENTITY(numeric,1,1) as ID1516 into ' + @tmpTableName + ' from' + @sqlQuery
end
exec(@sqlQuery) --//建立并初始化临时表数据

declare @indexStart varchar(20),@indexEnd varchar(20)
set @indexStart = cast((@pageIndex-1)*@pageSize+1 as varchar(20)) --//数据起始行ID
set @indexEnd = cast(@pageIndex * @pageSize as varchar(20)) --//数据结束行ID

exec('select * from ' + @tmpTableName + ' where ID1516 between ' + @indexStart + ' and ' + @indexEnd) --//检索该页数据

exec('select max(ID1516) as recordCount from ' + @tmpTableName) --//提取总条数

exec('drop table ' + @tmpTableName) --//删除临时表

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[/code]

赞 (0)

相关推荐

  • sqlserver 存储过程分页(按多条件排序)

    cs页面调用代码: 复制代码 代码如下: public int TotalPage = 0; public int PageCurrent = 1; public int PageSize = 25; public int RowsCount = 0; string userid, username; public DataTable dt = new DataTable(); public string path, userwelcome; public string opt,cid; pro

  • 浅谈基于SQL Server分页存储过程五种方法及性能比较

    在SQL Server数据库操作中,我们常常会用到存储过程对实现对查询的数据的分页处理,以方便浏览者的浏览. 创建数据库data_Test : create database data_Test GO use data_Test GO create table tb_TestTable --创建表 ( id int identity(1,1) primary key, userName nvarchar(20) not null, userPWD nvarchar(20) not null, u

  • MSSQL分页存储过程完整示例(支持多表分页存储)

    本文实例讲述了MSSQL分页存储过程.分享给大家供大家参考,具体如下: USE [DB_Common] GO /****** 对象: StoredProcedure [dbo].[Com_Pagination] 脚本日期: 03/09/2012 23:46:20 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /************************************************************

  • MSSQL MySQL 数据库分页(存储过程)

    先看看单条 SQL 语句的分页 SQL 吧. 方法1: 适用于 SQL Server 2000/2005 复制代码 代码如下: SELECT TOP 页大小 * FROM table1 WHERE id NOT IN ( SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id ) ORDER BY id 方法2: 适用于 SQL Server 2000/2005 复制代码 代码如下: SELECT TOP 页大小 * FROM table1 WHERE

  • 五种SQL Server分页存储过程的方法及性能比较

    在SQL Server数据库操作中,我们常常会用到存储过程对实现对查询的数据的分页处理,以方便浏览者的浏览.本文我们总结了五种SQL Server分页存储过程的方法,并对其性能进行了比较,接下来就让我们来一起了解一下这一过程. 创建数据库data_Test : create database data_Test GO use data_Test GO create table tb_TestTable --创建表 ( id int identity(1,1) primary key, userN

  • SQL Server 分页查询通用存储过程(只做分页查询用)

    自开始做项目以来,一直在用.这段存储过程的的原创者(SORRY,忘记名字了),写得这段SQL代码很不错,我在这个基础上,按照我的习惯以及思维方式,调整了代码,只做分页查询用. /*---------------------------------------------- *procedure name : P_PageResult * author :Fay * create date : 2014-07-18 */ CREATE PROCEDURE prcPageResult -- 获得某一

  • MsSql 存储过程分页代码 [收集多篇]

    复制代码 代码如下: --使用说明 本代码适用于MsSql2000,对于其它数据库也可用.但没必要 --创建存储过程 CREATE PROCEDURE pagination @tblName varchar(255), -- 表名 @strGetFields varchar(1000) = '*', -- 需要返回的列 @fldName varchar(255)='', -- 排序的字段名(可包含如TABLE.FLDNAME形式) @PageSize int = 10, -- 页尺寸 @Page

  • sqlserver2005利用临时表和@@RowCount提高分页查询存储过程性能示例分享

    最近发现现有框架的通用查询存储过程的性能慢,于是仔细研究了下代码: 复制代码 代码如下: Alter PROCEDURE [dbo].[AreaSelect]    @PageSize int=0,    @CurrentPage int=1,    @Identifier int=NULL,    @ParentId int=NULL,    @AreaLevel int=NULL,    @Children int=NULL,    @AreaName nvarchar(50)=NULL, 

  • mssql 高效的分页存储过程分享

    最近在做一个几百万条数据的分页查询,研究了各种方案,在本机上用项目的实际数据库做测试,测试过程 is very 痛苦,不堪回首ing.现在废话不多说,直接上结果,相信这也是大多数搜索答案的人最愿意看的方式. 以下是存储过程的代码: 复制代码 代码如下: CREATE PROCEDURE [dbo].[P_GridViewPager] (     @recordTotal INT OUTPUT,            --输出记录总数     @viewName VARCHAR(800),    

  • SQL Server的通用分页存储过程 未使用游标，速度更快！

    正常情况下,SQL Server服务器上会对使用频率大的Table建立合适的索引 这样能大幅度的提高数据库本身的数据检索速度,建立索引的方法就不细说了 如果需要返回大量数据,从几百行到几万行,甚至几十万行数据 这时会发现响应速度越来越慢,甚至发生响应超时的错误 为了解决这种大数据量请求的问题,就不得不使用分页模式了 在这方面,JDBC就强悍得多,它可以将指定的行数和SQL请求一并发送给SQL Server,这样只返回分页后的数据,JDBC的原理还不清楚,但在实际使用中,速度还是非常快的 如果没办

  • SQL Server 2005通用分页存储过程及多表联接应用

    这是不久前写的一个分页存储过程,可应用于SQL Server 2005上面: 复制代码 代码如下: if object_ID('[proc_SelectForPager]') is not null Drop Procedure [proc_SelectForPager] Go Create Proc proc_SelectForPager ( @Sql varchar(max) , @Order varchar(4000) , @CurrentPage int , @PageSize int,

  • 深入sql server 2005 万能分页存储过程的详解

    –建立主表临时表CREATE TABLE #temp(rownumber bigint,orderseqno VARCHAR(36),goodsname VARCHAR(50),companyname VARCHAR(100))–建立子表临 时表CREATE TABLE #detail(orderseqno VARCHAR(36),detailid UNIQUEIDENTIFIER,unitprice DECIMAL(12,2),Qty int)–插入主表数据到主表临时表insert into

  • 基于Sql Server通用分页存储过程的解决方法

    sql server2000分页方式很多,效率吗  当到达10万级以上就有些吃力了,这里暂时不例出来了 sql server2005以上就有了row_number 也是一大进步,详情如下参考 复制代码 代码如下: Sql Server2005通用分页存储过程 CREATE PROCEDURE [dbo].[Common_GetPagedList](@TableName nvarchar(100), --表名@ColumnNames nvarchar(1000) = '*', --字段名集合(全部

  • 通用分页存储过程，源码共享，大家共同完善

    好久没有上来写点东西了,今天正好有空,共享一些个人心得,就是关于分页的存储过程,这个问题应该是老生重谈了,网上的通用存储过程的类型已经够多了,但是,好象看到的基本上不能够满足一些复杂的SQL语句的分页(也可能是我不够见多识广啊,呵呵),比如下面这句:  select '' as CheckBox, A.TargetID, A.TargetPeriod, Convert(varchar(10), B.BeginDate, 120) as BeginDate,      Convert(varcha

  • SQL Server使用row_number分页的实现方法

    本文为大家分享了SQL Server使用row_number分页的实现方法,供大家参考,具体内容如下 1.首先是 select ROW_NUMBER() over(order by id asc) as 'rowNumber', * from table1 生成带序号的集合 2.再查询该集合的 第 1  到第 5条数据 select * from (select ROW_NUMBER() over(order by id asc) as 'rowNumber', * from table1) a

  • sql server编写通用脚本实现获取一年前日期的方法

    问题: 在数据库编程开发中,有时需要获取一年前的日期,以便以此为时间的分界点,查询其前后对应的数据量.例如: 1. 想查询截止到一年前当天0点之前的数据量,以及一年前当天0点开始到现在的数据量. 2. 想查询截止到一年前当天24点之前的数据量,以及一年前当天24点开始到现在的数据量. 3. 想查询截止到一年前当月1日0点之前的数据量,以及一年前当月1日0点开始到现在的数据量. 4. 想查询截止到一年前当月最后一天24点之前的数据量,以及一年前当月最后一天24点开始到现在的数据量. 以上这四种情况

  • SQL Server中函数、存储过程与触发器的用法

    一.函数 函数分为(1)系统函数,(2)自定义函数. 其中自定义函数又可以分为(1)标量值函数(返回单个值),(2)表值函数(返回查询结果) 本文主要介绍自定义函数的使用. (1)编写一个函数求该银行的金额总和 create function GetSumCardMoney() returns money as begin declare @AllMOney money select @AllMOney = (select SUM(CardMoney) from BankCard) return

  • SQL Server两种分页的存储过程使用介绍

    SQL Server两种分页的存储过程使用介绍

    由于现在很多的企业招聘的笔试都会让来招聘的写一个分页的存储过程,有的企业甚至要求应聘者用两种方式实现分页,如果没有在实际项目中使用过分页,那么很多的应聘者都会出现一定的问题,下面介绍两种分页的方法. 一. 以学生表为例,在数据库中有一个Student表,字段有StudentNo, ,LoginPwd, StudentName,Sex,ClassId,Phone,Address,BornDate,Email,isDel 要求:查询学生的信息,每页显示5条记录 二.第一种方式分页:利用子查询 not

  • [数据库] 通用分页存储过程第1/5页

    分页存储过程共有四种方式可以实现,行计数.游标.升序-降序.子查询 我记得曾经有人测试过这四种方式的效率分别是 从性能最好到最差的顺序进行的--行计数.游标.升序-降序.子查询 以下是我收集的一些资料供大家参考 QUOTE: 原文地址:http://www.codeproject.com/aspnet/PagingLarge.asp 作者:Jasmin Muharemovic  译者:Tony Qu 下载: 介绍 在Web应用程序中,对一个大数据库结果集进行分页已经是一个家喻户晓的问题了.简单的

随机推荐