killergo的專欄

最近因?yàn)樯晕⒂悬c(diǎn)空閑時(shí)間，所以想了下在sql server平臺(tái)用存儲(chǔ)過程的分頁方式，現(xiàn)在列示在下面。

實(shí)際測(cè)試時(shí)，在15000條數(shù)據(jù)情況下兩者性能大體相當(dāng)，在20000-30000條數(shù)據(jù)的情況下前者明顯比后者性能更佳。更大數(shù)據(jù)量沒有進(jìn)行測(cè)試了。

注意，數(shù)據(jù)表里面是否有 鍵和索引 對(duì)性能的影響相當(dāng)大-----------------------------------------------------第一種:

/*第一個(gè)參數(shù)是每頁條數(shù)，第二個(gè)參數(shù)是目標(biāo)頁碼*/

CREATE proc sp_fixpage @pagesize int,@destpage int; as set nocount ondeclare @id intdeclare @startid int

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

set rowcount @startidselect @id = id from t_member

set rowcount @pagesizeset nocount offselect * from t_member where id > @id order by idGO

第二種:

CREATE PROCEDURE sp_fixpage1 @pagesize int ,@destpage intas 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 = 1set @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 myCursorfetch 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 @nowIDend

close myCursordeallocate myCursor

set nocount off select * from #myTabledrop table #myTableGO