SQL Server 三种分页方法比较实验

SQL Server 三种分页方法比较实验

每一个系统都避免不了列表页，而列表页一般都要求分页显示。虽然所有语言都提供了内部的分页功能，但数据量一多，效率便非常劲差，尤其在B/S架构中。影响原因可以总结为两个：
1、程序语言分页，每次都要读取数据库中的所有相关数据，而只需显示10、20条，大量数据并没有用到。
2、如果服务器或者客户端提供了数据缓存，可以稍微优化一点，但有一个很明显的现象，没有一个客户需要一次看到那么多数据，他可能只需要一条或者几条数据。

所以注意到优化性能的程序员一般都不会使用程序语言进行分页。分页的任务由应用服务器或者客户端转移到恶劣数据库服务器，而数据库服务器的分页方式一般都是由SQL语句（可以术存储过程）实现，根据以往SQL Server的学习经验，总结分页SQL主要有三种。

1、使用两个select和两个order，双倒序：比如你有查找相关记录中10001-10020的20条记录。你先按照某字段排序，查找前10020条记录，形成数据集，再进行倒序查找前20条。这种方法比较简单，但进行多字段排序时，有时候会出现莫名奇妙的错误（可能我不够经验）。

2、使用两个select，并产生临时序号：一样比如你有查找相关记录中10001-10020的20条记录。你首先查找所有相关数据，并增加新的列储存记录临时序号，形成数据集，再根据查找数据集，而条件增加了临时序号在10001-10020之间。这种方法必须使用陌生的产生临时序号函数，而产生临时序号的函数只有SQL Serrver 2005 才支持。

3、创建临时表，产生临时ID：一样比如你有查找相关记录中10001-10020的20条记录。你可以首先创建一个只有自增ID和你需要查找相关记录的自己字段的临时表，并将你查找记录的主键填入临时表。再查询临时表，增加条件临时序号在10001-10020之间。最后删除临时表。这种方法最复杂，但可扩展的空间比较大，性能的优化的潜力也应该比较强。

下面在对这三种办法进行实际例子说明：

实验环境：
Intel(R) Core(TM)2 CPU 4300 @ 1.80GHz 1.80GHz 1.96G内存
Microsoft Windwos XP Professional SP2
Microsoft SQL Server Management Studio Express
还有一些乱七八糟的软件和服务。

数据库表Test，要12个字段，一个自增ID（主键），一个smallint的type字段和10个无关紧要的nchar(10)字段。总记录有700000条。

SQL语句如下：

-- --------------两个select 加两个order，双倒序（27秒钟）
select   top   20   *   from  ( select   top   349980   *   from  Test  where  type  > 5   order   by  type  desc )  as   temp   order   by  type  desc

-- --------------两个select 产生临时序号（1秒钟）
select   *   from  (
select   *  ,ROW_NUMBER()  OVER ( ORDER   BY  type,ID)  AS  RowRank  from  Test  where  type  > 5 )  as   temp
where  RowRank  between   350001   and   350020

-- --------------临时表，create＋insert＋select＋drop（3秒钟）
-- --------------临时表，create＋insert＋select（2秒钟）
CREATE   TABLE  #PageIndexForUsers
(
    IndexID  int   IDENTITY  ( 0 ,  1 )  NOT   NULL ,
    ID  int
)
INSERT   INTO  #PageIndexForUsers (ID)
SELECT  ID
FROM  Test
WHERE  type  > 5
ORDER   BY  type,ID

SELECT  T. *   FROM  Test T, #PageIndexForUsers p
WHERE   p.ID  =  T.ID  AND
       p.IndexId  between   350000   and   350019

drop   table  #PageIndexForUsers

运行结果占用了时间是第一种方式用了27秒，第二种用了1秒，第三种不加drop用了2秒，加drop用了3秒。下面的附件是数据库文件，大家有兴趣可以下载试试。

posted on 2007-09-27 09:48  varmc 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/varmc/archive/2007/09/27/907536.html