关于sql 几种分页查询效率的存储过程

创建数据库和表向表中插入十万条数据

--创建数据库
create  database   pageTest
go
create table T_page
(
 id  int identity(1,1)  not null,
 t_Number int null,
 t_Name nvarchar(50)  null,
 t_dataTime datetime
)



go
declare  @i int,@randNum int,@N_name nvarchar(50)
set  @i=0
set  @randNum=0
while(@i<100000)
    begin
        set @N_name=LEFT(NEWID(),10);
        set @randNum= cast(FLOOR( rand()*100000)  as int);
        insert into  T_page  values(@randNum,@N_name ,GETDATE()) ;
        set @i=@i+1;
    end

if(exists(select * from sys.objects where name='pro_page_Top'))--判断是否存在存储过程
drop proc pro_page_Top --删除存储过程
go
--创建名为pro_page_top带参数的存储过程(轻微)

create proc pro_page_Top(@Index int, @PageSize int, @type nvarchar(50))
as
declare @begin_date datetime,@end_date datetime,@coumlnName  nvarchar(50) --定义参数nvarchar类型
  if @type='top'
      begin
      
      set @begin_date=GETDATE();
         --select top (@PageSize) * from T_page  where id not in (select top ((@Index-1)*(@PageSize)) Id from T_page )
       select top (5) * from T_page  where id not in (select top 50000 Id from T_page )

      set @end_date=GETDATE();    
        select datediff(ms,@begin_date,@end_date) as  '毫秒/top not in';
      end
  else if @type='Max'
     begin
     set @begin_date=GETDATE();
         select top (5)  * from  T_page  where id>
         (
          select MAX(Id) from (select (50000) id from  T_page ) a
         )
         --select top (@PageSize)  * from  T_page  where id>
         --(
         -- select MAX(Id) from (select ((@Index-1)*(@PageSize)) id from  T_page  ) a
         --)
         set @end_date=GETDATE();    
        select datediff(ms,@begin_date,@end_date) as  '毫秒/Max';
     end
  else if @type='not_exists'
  begin
  set @begin_date=GETDATE();
    --select top (@PageSize) * from  T_page where not  exists
    --( select 1 from(select top((@Index-1)*@PageSize) id from T_page order by id) a where a.id=T_page.id  )      
    --order by id  
    select top (5) * from  T_page where not  exists
    ( select 1 from(select top(50000) id from T_page order by id) a where a.id=T_page.id  )      
    order by id  
    set @end_date=GETDATE();
    select datediff(ms,@begin_date,@end_date) as  '毫秒/not_exists';
  end
  else if @type='row_number'
      begin
          set @begin_date=GETDATE();
           select top  (5)  * from   ( select ROW_NUMBER() over (order by id) rownumber,* from T_page ) a  where  rownumber> (50000);

            --select top  (@PageSize)  * from   ( select ROW_NUMBER() over (order by id) rownumber,* from T_page ) a  where  rownumber> ((@PageSize-1)*@Index);
          set @end_date=GETDATE();
          select datediff(ms,@begin_date,@end_date) as  '毫秒/row_number';
      end
   else if @type='row_number_and'
   begin          
          set @begin_date=GETDATE();
           --select * from  (select  ROW_NUMBER() over (order by id)  rownumber,* from T_page ) a  where rownumber>((@PageSize-1)*@Index) and  rownumber<(@PageSize*@Index)
           select * from  (select  ROW_NUMBER() over (order by id)  rownumber,* from T_page ) a  where rownumber>(49995) and  rownumber<(50000)

          set @end_date=GETDATE();
          select datediff(ms,@begin_date,@end_date) as  '毫秒/row_number_and';
      end
  else if @type='no_number_between'
  begin
  set @begin_date=GETDATE();
      select * from  ( select ROW_NUMBER() over (order by id )  rownumber,* from T_page )  a where  rownumber between (49995)  and  (50000)
  set @end_date=GETDATE();
    select datediff(ms,@begin_date,@end_date) as  '毫秒/no_number_between';
  end
  else
     print  @type
   -- exec ('select datediff(ms,2012-12-12,2012-12-13) as '+ @coumlnName+'')
  GO

 

---执行存储过程

declare @str nvarchar(50)
 set  @str='top';
 exec  pro_page_Top 1000,5,@str
 exec  pro_page_Top 1000,5,'Max'
 exec  pro_page_Top 1000,5,'not_exists'
 exec  pro_page_Top 1000,5,'row_number'
 exec  pro_page_Top 1000,5,'row_number_and'
 exec  pro_page_Top 1000,5,'no_number_between'

---结果比较

id          t_Number    t_Name                                             t_dataTime
----------- ----------- -------------------------------------------------- -----------------------
50001       57183       A8CD6B69-4                                         2012-12-06 17:40:19.793
50002       34946       6E94FABB-9                                         2012-12-06 17:40:19.793
50003       34375       09590DF7-6                                         2012-12-06 17:40:19.793
50004       12540       16A1C233-C                                         2012-12-06 17:40:19.793
50005       99512       A61A51F5-E                                         2012-12-06 17:40:19.793

毫秒/top not in
-------------
673


id          t_Number    t_Name                                             t_dataTime
----------- ----------- -------------------------------------------------- -----------------------
50001       57183       A8CD6B69-4                                         2012-12-06 17:40:19.793
50002       34946       6E94FABB-9                                         2012-12-06 17:40:19.793
50003       34375       09590DF7-6                                         2012-12-06 17:40:19.793
50004       12540       16A1C233-C                                         2012-12-06 17:40:19.793
50005       99512       A61A51F5-E                                         2012-12-06 17:40:19.793


毫秒/Max
-----------
373

(1 row(s) affected)

id          t_Number    t_Name                                             t_dataTime
----------- ----------- -------------------------------------------------- -----------------------
50001       57183       A8CD6B69-4                                         2012-12-06 17:40:19.793
50002       34946       6E94FABB-9                                         2012-12-06 17:40:19.793
50003       34375       09590DF7-6                                         2012-12-06 17:40:19.793
50004       12540       16A1C233-C                                         2012-12-06 17:40:19.793
50005       99512       A61A51F5-E                                         2012-12-06 17:40:19.793


毫秒/not_exists
-------------
5126


rownumber            id          t_Number    t_Name                                             t_dataTime
-------------------- ----------- ----------- -------------------------------------------------- -----------------------
50001                50001       57183       A8CD6B69-4                                         2012-12-06 17:40:19.793
50002                50002       34946       6E94FABB-9                                         2012-12-06 17:40:19.793
50003                50003       34375       09590DF7-6                                         2012-12-06 17:40:19.793
50004                50004       12540       16A1C233-C                                         2012-12-06 17:40:19.793
50005                50005       99512       A61A51F5-E                                         2012-12-06 17:40:19.793


毫秒/row_number
-------------
2936


rownumber            id          t_Number    t_Name                                             t_dataTime
-------------------- ----------- ----------- -------------------------------------------------- -----------------------
49996                49996       61624       337F96BE-8                                         2012-12-06 17:40:19.793
49997                49997       63107       74A71368-8                                         2012-12-06 17:40:19.793
49998                49998       46804       E4FD9503-9                                         2012-12-06 17:40:19.793
49999                49999       24426       8265E237-B                                         2012-12-06 17:40:19.793


毫秒/row_number_and
-----------------
2750



rownumber            id          t_Number    t_Name                                             t_dataTime
-------------------- ----------- ----------- -------------------------------------------------- -----------------------
49996                49996       61624       337F96BE-8                                         2012-12-06 17:40:19.793
49997                49997       63107       74A71368-8                                         2012-12-06 17:40:19.793
49998                49998       46804       E4FD9503-9                                         2012-12-06 17:40:19.793
49999                49999       24426       8265E237-B                                         2012-12-06 17:40:19.793
50000                50000       81827       89672385-B                                         2012-12-06 17:40:19.793


毫秒/no_number_between
--------------------
2563