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

    高效的SQLSERVER分页查询_oracle 高效分页查询

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

    Sqlserver数据库分页查询一直是Sqlserver的短板，闲来无事，想出几种方法，假设有表ARTICLE,字段ID、YEAR...(其他省略)，数据53210条(客户真实数据，量不大)，分页查询每页30条，查询第1500页(即第45001-45030条数据)，字段ID聚集索引，YEAR无索引，Sqlserver版本：2008R2

    第一种方案、最简单、普通的方法：

    复制代码代码如下:

    SELECT TOP 30 * FROM ARTICLE WHERE ID NOT IN(SELECT TOP 45000 ID FROM ARTICLE ORDER BY YEAR DESC, ID DESC) ORDER BY YEAR DESC,ID DESC

    平均查询100次所需时间：45s

    第二种方案：

    复制代码代码如下:

    SELECT * FROM ( SELECT TOP 30 * FROM (SELECT TOP 45030 * FROM ARTICLE ORDER BY YEAR DESC, ID DESC) f ORDER BY f.YEAR ASC, f.ID DESC) s ORDER BY s.YEAR DESC,s.ID DESC

    平均查询100次所需时间：138S

    第三种方案：

    复制代码代码如下:

    SELECT * FROM ARTICLE w1,

    (

    SELECT TOP 30 ID FROM

    (

    SELECT TOP 50030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC

    ) w ORDER BY w.YEAR ASC, w.ID ASC

    ) w2 WHERE w1.ID = w2.ID ORDER BY w1.YEAR DESC, w1.ID DESC

    平均查询100次所需时间：21S

    第四种方案：

    复制代码代码如下:

    SELECT * FROM ARTICLE w1

    WHERE ID in

    (

    SELECT top 30 ID FROM

    (

    SELECT top 45030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC

    ) w ORDER BY w.YEAR ASC, w.ID ASC

    )

    ORDER BY w1.YEAR DESC, w1.ID DESC

    平均查询100次所需时间：20S

    第五种方案：

    复制代码代码如下:

    SELECT w2.n, w1.* FROM ARTICLE w1, ( SELECT TOP 50030 row_number() OVER (ORDER BY YEAR DESC, ID DESC) n, ID FROM ARTICLE ) w2 WHERE w1.ID = w2.ID AND w2.n > 50000 ORDER BY w2.n ASC

    平均查询100次所需时间：15S

    查询第1000-1030条记录

    第一种方案：

    复制代码代码如下:

    SELECT TOP 30 * FROM ARTICLE WHERE ID NOT IN(SELECT TOP 1000 ID FROM ARTICLE ORDER BY YEAR DESC, ID DESC) ORDER BY YEAR DESC,ID DESC

    平均查询100次所需时间：80s

    第二种方案：

    复制代码代码如下:

    SELECT * FROM ( SELECT TOP 30 * FROM (SELECT TOP 1030 * FROM ARTICLE ORDER BY YEAR DESC, ID DESC) f ORDER BY f.YEAR ASC, f.ID DESC) s ORDER BY s.YEAR DESC,s.ID DESC

    平均查询100次所需时间：30S

    第三种方案：

    复制代码代码如下:

    SELECT * FROM ARTICLE w1,

    (

    SELECT TOP 30 ID FROM

    (

    SELECT TOP 1030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC

    ) w ORDER BY w.YEAR ASC, w.ID ASC

    ) w2 WHERE w1.ID = w2.ID ORDER BY w1.YEAR DESC, w1.ID DESC

    平均查询100次所需时间：12S

    第四种方案：

    复制代码代码如下:

    SELECT * FROM ARTICLE w1

    WHERE ID in

    (

    SELECT top 30 ID FROM

    (

    SELECT top 1030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC

    ) w ORDER BY w.YEAR ASC, w.ID ASC

    )

    ORDER BY w1.YEAR DESC, w1.ID DESC

    平均查询100次所需时间：13S

    第五种方案：

    复制代码代码如下:

    SELECT w2.n, w1.* FROM ARTICLE w1,( SELECT TOP 1030 row_number() OVER (ORDER BY YEAR DESC, ID DESC) n, ID FROM ARTICLE) w2 WHERE w1.ID = w2.ID AND w2.n > 1000 ORDER BY w2.n ASC

    平均查询100次所需时间：14S

    由此可见在查询页数靠前时，效率3>4>5>2>1，页码靠后时5>4>3>1>2，再根据用户习惯，一般用户的检索只看最前面几页，因此选择3 4 5方案均可，若综合考虑方案5是最好的选择，但是要注意SQL2000不支持row_number()函数，由于时间和条件的限制没有做更深入、范围更广的测试，有兴趣的可以仔细研究下。

    以下是根据第四种方案编写的一个分页存储过程：

    复制代码代码如下:

    if exists (select * from dbo.sysobjects where id = object_id(N"[dbo].[sys_Page_v2]") and OBJECTPROPERTY(id, N"IsProcedure") = 1)

    drop procedure [dbo].[sys_Page_v2]

    GO

    CREATE PROCEDURE [dbo].[sys_Page_v2]

    @PCount int output, --总页数输出

    @RCount int output, --总记录数输出

    @sys_Table nvarchar(100), --查询表名

    @sys_Key varchar(50), --主键

    @sys_Fields nvarchar(500), --查询字段

    @sys_Where nvarchar(3000), --查询条件

    @sys_Order nvarchar(100), --排序字段

    @sys_Begin int, --开始位置

    @sys_PageIndex int, --当前页数

    @sys_PageSize int --页大小

    AS

    SET NOCOUNT ON

    SET ANSI_WARNINGS ON

    IF @sys_PageSize < 0 OR @sys_PageIndex < 0

    BEGIN

    RETURN

    END

    DECLARE @new_where1 NVARCHAR(3000)

    DECLARE @new_order1 NVARCHAR(100)

    DECLARE @new_order2 NVARCHAR(100)

    DECLARE @Sql NVARCHAR(4000)

    DECLARE @SqlCount NVARCHAR(4000)

    DECLARE @Top int

    if(@sys_Begin <=0)

    set @sys_Begin=0

    else

    set @sys_Begin=@sys_Begin-1

    IF ISNULL(@sys_Where,"") = ""

    SET @new_where1 = " "

    ELSE

    SET @new_where1 = " WHERE " + @sys_Where

    IF ISNULL(@sys_Order,"") <> ""

    BEGIN

    SET @new_order1 = " ORDER BY " + Replace(@sys_Order,"desc","")

    SET @new_order1 = Replace(@new_order1,"asc","desc")

    SET @new_order2 = " ORDER BY " + @sys_Order

    END

    ELSE

    BEGIN

    SET @new_order1 = " ORDER BY ID DESC"

    SET @new_order2 = " ORDER BY ID ASC"

    END

    SET @SqlCount = "SELECT @RCount=COUNT(1),@PCount=CEILING((COUNT(1)+0.0)/"

    + CAST(@sys_PageSize AS NVARCHAR)+") FROM " + @sys_Table + @new_where1

    EXEC SP_EXECUTESQL @SqlCount,N"@RCount INT OUTPUT,@PCount INT OUTPUT",

    @RCount OUTPUT,@PCount OUTPUT

    IF @sys_PageIndex > CEILING((@RCount+0.0)/@sys_PageSize) --如果输入的当前页数大于实际总页数,则把实际总页数赋值给当前页数

    BEGIN

    SET @sys_PageIndex = CEILING((@RCount+0.0)/@sys_PageSize)

    END

    set @sql = "select "+ @sys_fields +" from " + @sys_Table + " w1 "

    + " where "+ @sys_Key +" in ("

    +"select top "+ ltrim(str(@sys_PageSize)) +" " + @sys_Key + " from "

    +"("

    +"select top " + ltrim(STR(@sys_PageSize * @sys_PageIndex + @sys_Begin)) + " " + @sys_Key + " FROM "

    + @sys_Table + @new_where1 + @new_order2

    +") w " + @new_order1

    +") " + @new_order2

    print(@sql)

    Exec(@sql)

    GO

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

    最新文章

    热点文章

    推荐访问

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

    本文高效的SQLSERVER分页查询_oracle 高效分页查询由山茶花美文网整理提供，版权归原作者、原出处所有。

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