浅谈基于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,
userEmail nvarchar(40) null
)
GO

插入数据：

set identity_insert tb_TestTable on
declare @count int
set@count=1
while @count<=2000000
begin
insert into tb_TestTable(id,userName,userPWD,userEmail) values(@count,'admin','admin888','lli0077@yahoo.com.cn')
set @count=@count+1
end
set identity_insert tb_TestTable off

1、利用select top 和select not in进行分页

具体代码如下：

create procedure proc_paged_with_notin --利用select top and select not in
(
@pageIndex int, --页索引
@pageSize int  --每页记录数
)
as
begin
set nocount on;
declare @timediff datetime --耗时
declare @sql nvarchar(500)
select @timediff=Getdate()
set @sql='select top '+str(@pageSize)+' * from tb_TestTable where(ID not in(select top '+str(@pageSize*@pageIndex)+' id from tb_TestTable order by ID ASC)) order by ID'
execute(@sql) --因select top后不支技直接接参数，所以写成了字符串@sql
select datediff(ms,@timediff,GetDate()) as 耗时
set nocount off;
end

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

create procedure proc_paged_with_selectMax --利用select top and select max(列)
(
@pageIndex int, --页索引
@pageSize int  --页记录数
)
as
begin
set nocount on;
declare @timediff datetime
declare @sql nvarchar(500)
select @timediff=Getdate()
set @sql='select top '+str(@pageSize)+' * From tb_TestTable where(ID>(select max(id) From (select top '+str(@pageSize*@pageIndex)+' id From tb_TestTable order by ID) as TempTable)) order by ID'
execute(@sql)
select datediff(ms,@timediff,GetDate()) as 耗时
set nocount off;
end

3、利用select top和中间变量

create procedure proc_paged_with_Midvar --利用ID>最大ID值和中间变量
(
@pageIndex int,
@pageSize int
)
as
declare @count int
declare @ID int
declare @timediff datetime
declare @sql nvarchar(500)
begin
set nocount on;
select @count=0,@ID=0,@timediff=getdate()
select @count=@count+1,@ID=case when @count<=@pageSize*@pageIndex then ID else @ID end from tb_testTable order by id
set @sql='select top '+str(@pageSize)+' * from tb_testTable where ID>'+str(@ID)
execute(@sql)
select datediff(ms,@timediff,getdate()) as 耗时
set nocount off;
end

4、利用Row_number() 此方法为SQL server 2005中新的方法,利用Row_number()给数据行加上索引

create procedure proc_paged_with_Rownumber --利用SQL 2005中的Row_number()
(
@pageIndex int,
@pageSize int
)
as
declare @timediff datetime
begin
set nocount on;
select @timediff=getdate()
select * from (select *,Row_number() over(order by ID asc) as IDRank from tb_testTable) as IDWithRowNumber where IDRank>@pageSize*@pageIndex and IDRank<@pageSize*(@pageIndex+1)
select datediff(ms,@timediff,getdate()) as 耗时
set nocount off;
end

5、利用临时表及Row_number

create procedure proc_CTE --利用临时表及Row_number
(
@pageIndex int, --页索引
@pageSize int  --页记录数
)
as
set nocount on;
declare @ctestr nvarchar()
declare @strSql nvarchar()
declare @datediff datetime
begin
select @datediff=GetDate()
set @ctestr='with Table_CTE as
(select ceiling((Row_number() over(order by ID ASC))/'+str(@pageSize)+') as page_num,* from tb_TestTable)';
set @strSql=@ctestr+' select * From Table_CTE where page_num='+str(@pageIndex)
end
begin
execute sp_executesql @strSql
select datediff(ms,@datediff,GetDate())
set nocount off;
end

以上的五种方法中，网上说第三种利用select top和中间变量的方法是效率最高的。关于SQL Server分页存储过程五种方法及性能比较的全部内容就到此结束了，希望对大家有所帮助。

赞 (0)

相关推荐

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

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

  • 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 /************************************************************

  • 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 存储过程分页代码 [收集多篇]

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

  • 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

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

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

  • 五种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

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

  • 浅谈基于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

  • SQL server分页的4种方法示例（很全面）

    这篇博客讲的是SQL server的分页方法,用的SQL server 2012版本.下面都用pageIndex表示页数,pageSize表示一页包含的记录.并且下面涉及到具体例子的,设定查询第2页,每页含10条记录. 首先说一下SQL server的分页与MySQL的分页的不同,mysql的分页直接是用limit (pageIndex-1),pageSize就可以完成,但是SQL server 并没有limit关键字,只有类似limit的top关键字.所以分页起来比较麻烦. SQL serve

  • SQL server分页的四种方法思路详解(最全面教程)

    目录 方法一:三重循环 思路 方法二:利用max(主键) 方法三:利用row_number关键字 第四种方法:offset /fetch next(2012版本及以上才有) 总结   这篇博客讲的是SQL server的分页方法,用的SQL server 2012版本.下面都用pageIndex表示页数,pageSize表示一页包含的记录.并且下面涉及到具体例子的,设定查询第2页,每页含10条记录.   首先说一下SQL server的分页与MySQL的分页的不同,mysql的分页直接是用lim

  • c# 剔除sql语句'尾巴'的五种方法

    一:背景 1. 讲故事 这几天都在修复bug真的太忙了,期间也遇到了一个挺有趣bug,和大家分享一下,这是一块sql挺复杂的报表相关业务,不知道哪一位大佬在错综复杂的 嵌套 + 平行 if判断中sql拼接在某些UI组合下出问题了,最终的 sql 架构类似这样的. var sql = "select 1 union all select 2 union all select 3 union all"; 这种sql到数据库去肯定是报错的,有些朋友可能想说这还不简单,在相关的 if 判断中不

  • Java字符串拼接的五种方法及性能比较分析(从执行100次到90万次)

    Java字符串拼接的五种方法及性能比较分析(从执行100次到90万次)

    目录 > 源代码,供参考 > 测试结果: > 查看源代码,以及简单分析 > 字符串拼接一般使用"+",但是"+"不能满足大批量数据的处理,Java中有以下五种方法处理字符串拼接,各有优缺点,程序开发应选择合适的方法实现. 1. 加号 "+" 2. String contact() 方法 3. StringUtils.join() 方法 4. StringBuffer append() 方法 5. StringBuilder

  • 浅谈SQL Server中的三种物理连接操作(性能比较)

    浅谈SQL Server中的三种物理连接操作(性能比较)

    在SQL Server中,我们所常见的表与表之间的Inner Join,Outer Join都会被执行引擎根据所选的列,数据上是否有索引,所选数据的选择性转化为Loop Join,Merge Join,Hash Join这三种物理连接中的一种.理解这三种物理连接是理解在表连接时解决性能问题的基础,下面我来对这三种连接的原理,适用场景进行描述. 嵌套循环连接(Nested Loop Join) 循环嵌套连接是最基本的连接,正如其名所示那样,需要进行循环嵌套,嵌套循环是三种方式中唯一支持不等式连接的

  • 浅谈Android中多线程切换的几种方法

    我们知道,多线程是Android开发中必现的场景,很多原生API和开源项目都有多线程的内容,这里简单总结和探讨一下常见的多线程切换方式. 我们先回顾一下Java多线程的几个基础内容,然后再分析总结一些经典代码中对于线程切换的实现方式. 几点基础 多线程切换,大概可以切分为这样几个内容:如何开启多个线程,如何定义每个线程的任务,如何在线程之间互相通信. Thread Thread可以解决开启多个线程的问题. Thread是Java中实现多线程的线程类,每个Thread对象都可以启动一个新的线程,注

  • 浅谈Android Studio 解析XML的三种方法

    一丶概述 文件解析要求,json解析和xml解析,前面文章说过Json转实体类,这里就说说解析XML 内容: Android Studio 解析XML常见的三种方式:DOM PULL SAX (实现XML转实体类并打印输出) 效果演示: 二丶正文 SAX(Simple API for XML) 使用流式处理的方式,它并不记录所读内容的相关信息.它是一种以事件为驱动的XML API,解析速度快,占用内存少.使用回调函数来实现. 缺点是不能倒退. DOM(Document Object Model)

  • 浅谈Mysql大数据分页查询解决方案

    浅谈Mysql大数据分页查询解决方案

    目录 1.简介 2.分页插件使用 3.sql测试与分析 3.1 limit现象分析 3.2 解决之道 4 测试时走过的坑 4.1 百万数据内容都一样 4.2 写sql时,把"77"写成了77: 4.3 一个有趣的现象 总结 1.简介 之前,面阿里的时候,有个面试官问我有没有使用过分页查询,我说有,他说分页查询是有问题的,怎么解决:后来这个问题我没有回答出来:本着学习的态度,今天来解决一下这个问题: 2.分页插件使用 1.pom文件 <dependency> <grou

随机推荐