最近因为稍微有点空闲时间,所以想了下在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 |
|小黑屋|最新主题|手机版|微赢网络技术论坛 ( 苏ICP备08020429号 )
GMT+8, 2024-9-30 01:33 , Processed in 0.109766 second(s), 12 queries , Gzip On, MemCache On.
Powered by Discuz! X3.5
© 2001-2023 Discuz! Team.