首页 爱情文章 亲情文章 友情文章 生活随笔 校园文章 经典文章 人生哲理 励志文章 搞笑文章 心情日记
  • 爱情文章
  • 亲情文章
  • 友情文章
  • 生活随笔
  • 校园文章
  • 经典文章
  • 人生哲理
  • 励志文章
  • 搞笑文章
  • 心情日记
  • 英语文章
  • 范文大全
  • 作文大全
  • 新闻阅读
    •
    当前位置: 山茶花美文网 > 心情日记 > 正文

    【SQL,Server,大量数据的分页存储过程代码】 sql存储过程分页

    时间：2020-05-28来源：山茶花美文网 本文已影响 人 山茶花美文网手机站

    OK,我们首先创建一数据库：data_Test,并在此数据库中创建一表：tb_TestTable

    create database data_Test --创建数据库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

    然后我们在数据表中插入2000000条数据:

    --插入数据

    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@")

    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(400)

    declare @strSql nvarchar(400)

    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

    OK,至此，存储过程创建完毕，我们分别在每页10条数据的情况下在第2页，第1000页，第10000页，第100000页，第199999页进行测试，耗时单位：ms 每页测试5次取其平均值

    • 爱情文章
    • 亲情文章
    • 友情文章
    • 随笔
    • 哲理
    • 励志
    • 范文大全

    最新文章

    热点文章

    推荐访问

    军训远足心得体会 军训爬山心得体会 大学军训徒步拉练感想 军训徒步作文 军训徒步10公里感想 军训徒步心得体会 5分钟演讲中秋节节日 中秋一分钟演讲 中秋节演讲稿4分钟 中秋节演讲稿一分钟 中秋节演讲稿五分钟 3-5分钟 中秋节短篇演讲稿 中秋节演讲稿两分钟 中秋节的演讲稿3分钟 关于中秋节的演讲稿三分钟 孩子体验军训的心得 参加孩子军训活动的感受与收获 家长对孩子军训后的收获和感悟 小孩军训的收获与感悟 学生军训的收获与感悟 孩子军训成长过程家长感悟 孩子军训感悟 孩子军训心得体会 孩子军训的收获与感悟 兵役前训练心得体会 部队当兵的心得 新兵役前教育训练心得体会 预备役军训心得体会 新兵军训感悟

    本文【SQL,Server,大量数据的分页存储过程代码】 sql存储过程分页由山茶花美文网整理提供，版权归原作者、原出处所有。

    Copyright © 山茶花美文网 All Rights Reserved.