killergo的專欄

最近因為稍微有點空閑時間，所以想了下在sql server平臺用存儲過程的分頁方式，現在列示在下面。

實際測試時，在15000條數據情況下兩者性能大體相當，在20000-30000條數據的情況下前者明顯比后者性能更佳。更大數據量沒有進行測試了。

注意，數據表里面是否有 鍵和索引 對性能的影響相當大
-----------------------------------------------------
第一種:

/*第一個參數是每頁條數，第二個參數是目標頁碼*/

create proc sp_fixpage @pagesize int,@destpage int  as
set nocount on
declare @id int
declare @startid int

select @startid = (@destpage - 1)*@pagesize

set rowcount @startid
select @id = id from t_member

set rowcount @pagesize
set nocount off
select * from t_member where id > @id order by id
go

第二種:

create procedure sp_fixpage1 @pagesize int ,@destpage int
as
set nocount on
 
create table #mytable(
 [id] [int] not null ,
 [username] [varchar] (50) collate chinese_prc_ci_as not null ,
 [name] [varchar] (50) collate chinese_prc_ci_as null ,
 [origin] [int] null ,
 [latencybuydegree] [varchar] (50) collate chinese_prc_ci_as null ,
 [usertype] [varchar] (2) collate chinese_prc_ci_as null ,
 [email] [varchar] (50) collate chinese_prc_ci_as null ,
 [userlev] [int] null ,
 [regtime] [datetime] null ,
 [regmode] [bit] null ,
 [papernum] [varchar] (50) collate chinese_prc_ci_as null ,
 [userclass] [bit] null ,
 [password] [binary] (64) null ,
 [tel] [varchar] (50) collate chinese_prc_ci_as null ,
 [drass] [varchar] (150) collate chinese_prc_ci_as null ,
 [zip] [varchar] (50) collate chinese_prc_ci_as null ,
 [papernumlb] [int] null ,
 [opuser] [varchar] (50) collate chinese_prc_ci_as null ,
 [province] [varchar] (50) collate chinese_prc_ci_as null ,
 [birthdate] [datetime] null
) on [primary]

declare @temppos int
declare @abspos int
declare @nowid int

set @temppos = 1
set @abspos = 1

if @destpage > 1
    set @abspos = (@pagesize*(@destpage- 1) + 1)

   declare mycursor scroll cursor for
      select [id] from t_member order by id

open mycursor
fetch absolute @abspos from mycursor into @nowid

while (@@fetch_status = 0) and (@temppos <= @pagesize)
begin
  set @temppos = @temppos + 1
  insert into #mytable select * from t_member where [id] = @nowid
  fetch next from mycursor into @nowid
end

close mycursor
deallocate mycursor

set nocount off
 
select * from #mytable
drop table #mytable
go