提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

真正高效的SQLSERVER分页查询(多种方案)

SQLServer 查询 高效 方案 分页 多种 真正
2023-06-13 09:14:48 时间
Sqlserver数据库分页查询一直是Sqlserver的短板，闲来无事，想出几种方法，假设有表ARTICLE,字段ID、YEAR...(其他省略)，数据53210条(客户真实数据，量不大)，分页查询每页30条，查询第1500页（即第45001-45030条数据），字段ID聚集索引，YEAR无索引，Sqlserver版本：2008R2
第一种方案、最简单、普通的方法：
复制代码代码如下:

SELECTTOP30*FROMARTICLEWHEREIDNOTIN(SELECTTOP45000IDFROMARTICLEORDERBYYEARDESC,IDDESC)ORDERBYYEARDESC,IDDESC

平均查询100次所需时间：45s
第二种方案：
复制代码代码如下:

SELECT*FROM(　　SELECTTOP30*FROM(SELECTTOP45030*FROMARTICLEORDERBYYEARDESC,IDDESC)fORDERBYf.YEARASC,f.IDDESC)sORDERBYs.YEARDESC,s.IDDESC

平均查询100次所需时间：138S
第三种方案：
复制代码代码如下:
SELECT*FROMARTICLEw1,
(
SELECTTOP30IDFROM
(
SELECTTOP50030ID,YEARFROMARTICLEORDERBYYEARDESC,IDDESC
)wORDERBYw.YEARASC,w.IDASC
)w2WHEREw1.ID=w2.IDORDERBYw1.YEARDESC,w1.IDDESC

平均查询100次所需时间：21S
第四种方案：
复制代码代码如下:
SELECT*FROMARTICLEw1
WHEREIDin
(
SELECTtop30IDFROM
(
SELECTtop45030ID,YEARFROMARTICLEORDERBYYEARDESC,IDDESC
)wORDERBYw.YEARASC,w.IDASC
)
ORDERBYw1.YEARDESC,w1.IDDESC

平均查询100次所需时间：20S
第五种方案：
复制代码代码如下:
SELECTw2.n,w1.*FROMARTICLEw1,(　　
SELECTTOP50030row_number()OVER(ORDERBYYEARDESC,IDDESC)n,IDFROMARTICLE
)w2WHEREw1.ID=w2.IDANDw2.n>50000ORDERBYw2.nASC

平均查询100次所需时间：15S
查询第1000-1030条记录
第一种方案：
复制代码代码如下:
SELECTTOP30*FROMARTICLEWHEREIDNOTIN(SELECTTOP1000IDFROMARTICLEORDERBYYEARDESC,IDDESC)ORDERBYYEARDESC,IDDESC

平均查询100次所需时间：80s
第二种方案：
复制代码代码如下:
SELECT*FROM(　　
SELECTTOP30*FROM(SELECTTOP1030*FROMARTICLEORDERBYYEARDESC,IDDESC)fORDERBYf.YEARASC,f.IDDESC
)sORDERBYs.YEARDESC,s.IDDESC

平均查询100次所需时间：30S
第三种方案：
复制代码代码如下:
SELECT*FROMARTICLEw1,
(
SELECTTOP30IDFROM
(
SELECTTOP1030ID,YEARFROMARTICLEORDERBYYEARDESC,IDDESC
)wORDERBYw.YEARASC,w.IDASC
)w2WHEREw1.ID=w2.IDORDERBYw1.YEARDESC,w1.IDDESC

平均查询100次所需时间：12S
第四种方案：
复制代码代码如下:
SELECT*FROMARTICLEw1
WHEREIDin
(
SELECTtop30IDFROM
(
SELECTtop1030ID,YEARFROMARTICLEORDERBYYEARDESC,IDDESC
)wORDERBYw.YEARASC,w.IDASC
)
ORDERBYw1.YEARDESC,w1.IDDESC

平均查询100次所需时间：13S
第五种方案：
复制代码代码如下:
SELECTw2.n,w1.*FROMARTICLEw1,(　　
SELECTTOP1030row_number()OVER(ORDERBYYEARDESC,IDDESC)n,IDFROMARTICLE
)w2WHEREw1.ID=w2.IDANDw2.n>1000ORDERBYw2.nASC

平均查询100次所需时间：14S
由此可见在查询页数靠前时，效率3>4>5>2>1，页码靠后时5>4>3>1>2，再根据用户习惯，一般用户的检索只看最前面几页，因此选择345方案均可，若综合考虑方案5是最好的选择，但是要注意SQL2000不支持row_number()函数，由于时间和条件的限制没有做更深入、范围更广的测试，有兴趣的可以仔细研究下。
以下是根据第四种方案编写的一个分页存储过程：
复制代码代码如下:
ifexists(select*fromdbo.sysobjectswhereid=object_id(N"[dbo].[sys_Page_v2]")andOBJECTPROPERTY(id,N"IsProcedure")=1)
dropprocedure[dbo].[sys_Page_v2]
GO
CREATEPROCEDURE[dbo].[sys_Page_v2]
@PCountintoutput,--总页数输出
@RCountintoutput,--总记录数输出
@sys_Tablenvarchar(100),--查询表名
@sys_Keyvarchar(50),--主键
@sys_Fieldsnvarchar(500),--查询字段
@sys_Wherenvarchar(3000),--查询条件
@sys_Ordernvarchar(100),--排序字段
@sys_Beginint,--开始位置
@sys_PageIndexint,--当前页数
@sys_PageSizeint--页大小
AS
SETNOCOUNTON
SETANSI_WARNINGSON
IF@sys_PageSize<0OR@sys_PageIndex<0
BEGIN
RETURN
END
DECLARE@new_where1NVARCHAR(3000)
DECLARE@new_order1NVARCHAR(100)
DECLARE@new_order2NVARCHAR(100)
DECLARE@SqlNVARCHAR(4000)
DECLARE@SqlCountNVARCHAR(4000)
DECLARE@Topint
if(@sys_Begin<=0)
set@sys_Begin=0
else
set@sys_Begin=@sys_Begin-1
IFISNULL(@sys_Where,"")=""
SET@new_where1=""
ELSE
SET@new_where1="WHERE"+@sys_Where
IFISNULL(@sys_Order,"")<>""
BEGIN
SET@new_order1="ORDERBY"+Replace(@sys_Order,"desc","")
SET@new_order1=Replace(@new_order1,"asc","desc")
SET@new_order2="ORDERBY"+@sys_Order
END
ELSE
BEGIN
SET@new_order1="ORDERBYIDDESC"
SET@new_order2="ORDERBYIDASC"
END
SET@SqlCount="SELECT@RCount=COUNT(1),@PCount=CEILING((COUNT(1)+0.0)/"
+CAST(@sys_PageSizeASNVARCHAR)+")FROM"+@sys_Table+@new_where1
EXECSP_EXECUTESQL@SqlCount,N"@RCountINTOUTPUT,@PCountINTOUTPUT",
@RCountOUTPUT,@PCountOUTPUT
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("
+"selecttop"+ltrim(str(@sys_PageSize))+""+@sys_Key+"from"
+"("
+"selecttop"+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

相关文章