实测 C#+存储过程 百万级数据分页

为了寻找一个高效的存储过程，对C#+mssql如下存储过程分页进行了测试，测试条件：

环境：VS2008+MSSQ2000

数据量：200万

对如下两个存储过程进行测试：

第一个：

CREATE procedure GetPageNoStr
(
   @Sql nvarchar(2000), 
   @CurrentPage int=1,
   @PageSize int=20,
   @Order nvarchar(1000)=null
)
as
declare @P1 INT 
declare @Sql1 nvarchar(4000) Set @Sql1=stuff(@Sql,7,charindex('from',@Sql)-7,'  Count(*)  ')
if(@Order<>'' and @Order is not null)
set @sql=@sql+'  Order By  '+@Order
--//注意:@scrollopt = 1 会取得Select的时候的总行数 

 exec sp_cursoropen @P1 output, @Sql, @scrollopt =2
if (@P1 != 0) 
begin  
set @CurrentPage = (@CurrentPage - 1) * @PageSize + 1 

exec sp_cursorfetch @P1, 32, @CurrentPage, @PageSize 

exec sp_cursorclose @P1  
end
exec(@Sql1)
GO

第二个：

CREATE  PROCEDURE [dbo].[GetPageStr]
(
    @Table_Name               varchar(5000),          --表名
    @Sign_Record              varchar(50),       --主键
    @Filter_Condition         varchar(1000),         --筛选条件,不带where
    @Page_Size                int,               --页大小
    @Page_Index               int,          --页索引     
        @TaxisField               varchar(1000),            --排序字段
    @Taxis_Sign               int,               --排序方式 1为 DESC, 0为 ASC
            @Find_RecordList          varchar(1000),        --查找的字段
    @Record_Count             int                --总记录数
 )
 AS
BEGIN 
DECLARE  @Start_Number          int
DECLARE  @End_Number            int
DECLARE  @TopN_Number           int
 DECLARE  @sSQL                  varchar(8000)
                 if(@Find_RecordList='')
                 BEGIN
                      SELECT @Find_RecordList='*'
                 END
 SELECT @Start_Number =(@Page_Index-1) * @Page_Size
IF @Start_Number<=0
 SElECT @Start_Number=0
SELECT @End_Number=@Start_Number+@Page_Size
IF @End_Number>@Record_Count
 SELECT @End_Number=@Record_Count
 SELECT @TopN_Number=@End_Number-@Start_Number
 IF @TopN_Number<=0
 SELECT @TopN_Number=0
print @TopN_Number
 print @Start_Number
 print @End_Number
 print @Record_Count
                 IF @TaxisField=''
                 begin
                    select  @TaxisField=@Sign_Record
                 end
 IF @Taxis_Sign=0
  BEGIN
 IF @Filter_Condition=''
 BEGIN
 SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+' 
     WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
     WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
     ORDER BY '+@TaxisField+') order by '+@TaxisField+' DESC)order by '+@TaxisField
 END
ELSE
BEGIN
SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+' 
     WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
     WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
     WHERE '+@Filter_Condition+' ORDER BY '+@TaxisField+') and '+@Filter_Condition+' order by '+@TaxisField+' DESC) and '+@Filter_Condition+' order by '+@TaxisField
 END
END
ELSE
BEGIN
IF @Filter_Condition=''
BEGIN
SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+' 
         WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
         WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
         ORDER BY '+@TaxisField+' DESC) order by '+@TaxisField+')order by '+@TaxisField+' DESC'
     END
ELSE
BEGIN
SELECT @sSQL='SELECT '+@Find_RecordList+' FROM '+@Table_Name+' 
     WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@TopN_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
     WHERE '+@Sign_Record+' in (SELECT TOP '+CAST(@End_Number AS VARCHAR(10))+' '+@Sign_Record+' FROM '+@Table_Name+' 
     WHERE '+@Filter_Condition+' ORDER BY '+@TaxisField+' DESC) and '+@Filter_Condition+' order by '+@TaxisField+') and '+@Filter_Condition+' order by '+@TaxisField+' DESC'
 END
END
EXEC (@sSQL)
IF @@ERROR<>0
RETURN -3              
 RETURN 0
 END

 PRINT  @sSQL
GO

 

数据量为2百万条测试数据，测试时用的VS2008+MSSQL2000+AspNetPager进行分页的，测试情况如下：

第一种存储过程 执行时间：0.2624258

 

第二种存储过程 执行时间：0.001699

 

所以第二种存储过程 明显效率更高效，本文仅作参考，写下此文只是为了深夜测试工作做一个标记而已