高效的SQLSERVER分页查询

转载

穆雄雄的博客 2016-11-19 20:38:00

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，再根据用户习惯，一般用户的检索只看最前面几页，因此选择3 4 5方案均可，若综合考虑方案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