asp.net几十万条以上的数据查询!如何高效率分页!
几十万条以上的数据查询!如何高效率分页!
CREATE PROCEDURE [dbo].[ProcCustomPage] ( @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自己一直用的分页存储过程 希望对楼主有帮助