对SQL不太懂,网上找了一个通用的存储过程分页,不知性能如何,请大家帮我分析分析如下:CREATE PROCEDURE up_GetTopicList
@a_TableList Varchar(200),
@a_TableName Varchar(30),
@a_SelectWhere Varchar(500),
@a_SelectOrderId Varchar(20),
@a_SelectOrder Varchar(50),
@a_intPageNo int,
@a_intPageSize int,
@RecordCount int OUTPUT
as
/*定义局部变量*/
declare @intBeginID int
declare @intEndID int
declare @intRootRecordCount int
declare @intRowCount int
declare @TmpSelect NVarchar(600)
/*关闭计数*/
set nocount on
/*求总共根贴数*/ select @TmpSelect = 'set nocount on;select @SPintRootRecordCount = count(*) from '+@a_TableName+' '+@a_SelectWhere
execute sp_executesql
@TmpSelect,
N'@SPintRootRecordCount int OUTPUT',
@SPintRootRecordCount=@intRootRecordCount OUTPUTselect @RecordCount = @intRootRecordCount if (@intRootRecordCount = 0) --如果没有贴子,则返回零
return 0
/*判断页数是否正确*/
if (@a_intPageNo - 1) * @a_intPageSize > @intRootRecordCount
return (-1) /*求开始rootID*/
set @intRowCount = (@a_intPageNo - 1) * @a_intPageSize + 1
/*限制条数*/ select @TmpSelect = 'set nocount on;set rowcount @SPintRowCount;select @SPintBeginID = '+@a_SelectOrderId+' from '+@a_TableName+' '+@a_SelectWhere+' '+@a_SelectOrder
execute sp_executesql
@TmpSelect,
N'@SPintRowCount int,@SPintBeginID int OUTPUT',
@SPintRowCount=@intRowCount,@SPintBeginID=@intBeginID OUTPUT
/*结束rootID*/
set @intRowCount = @a_intPageNo * @a_intPageSize
/*限制条数*/ select @TmpSelect = 'set nocount on;set rowcount @SPintRowCount;select @SPintEndID = '+@a_SelectOrderId+' from '+@a_TableName+' '+@a_SelectWhere+' '+@a_SelectOrder
execute sp_executesql
@TmpSelect,
N'@SPintRowCount int,@SPintEndID int OUTPUT',
@SPintRowCount=@intRowCount,@SPintEndID=@intEndID OUTPUT
if @a_SelectWhere='' or @a_SelectWhere IS NULL
select @TmpSelect = 'set nocount off;set rowcount 0;select '+@a_TableList+' from '+@a_TableName+' where '+@a_SelectOrderId+' between '
else
select @TmpSelect = 'set nocount off;set rowcount 0;select '+@a_TableList+' from '+@a_TableName+' '+@a_SelectWhere+' and '+@a_SelectOrderId+' between 'if @intEndID > @intBeginID
select @TmpSelect = @TmpSelect+'@SPintBeginID and @SPintEndID'+' '+@a_SelectOrder
else
select @TmpSelect = @TmpSelect+'@SPintEndID and @SPintBeginID'+' '+@a_SelectOrder execute sp_executesql
@TmpSelect,
N'@SPintEndID int,@SPintBeginID int',
@SPintEndID=@intEndID,@SPintBeginID=@intBeginID return(@@rowcount)
--select @@rowcount
GO
shoutor(土人制造)土人老兄这是从你那弄的呀,难道你不记得了如果有百万数据量,你觉得这个过程怎么样
这个里面讲述了几种存储过程的效率
本来1个SQL语句就可以选择出来了不要过多去execsql
你看一下这个:经过多种对比,最终使用的下面的方法,200万条资料的表,返回10个字段,只需要2-3秒,当然,要适当建立索引
CREATE Procedure GetRecordByPage
(
@PageSize int, --每页的记录条数
@PageNumber int, --当前页面
@QuerySql varchar(6000),--部分查询字符串,如* From Test order by id desc,不要select
@KeyField varchar(2000) =null,
@CountSql varchar(8000)
)
AS
Begin
Declare @SqlTable AS varchar(6000)
Declare @SqlText AS Varchar(6000)
Set @SqlTable='Select Top '+CAST(@PageNumber*@PageSize AS varchar(30))+' '+@QuerySql
Set @SqlText=' Select Top '+Cast(@PageSize AS varchar(30))+' * From '
+'('+@SqlTable+') As TembTbA '
+'Where '+@KeyField+' Not In (Select Top '+CAST((@PageNumber-1)*@PageSize AS varchar(30))+' '+@KeyField+' From '
+'('+@SqlTable+') AS TempTbB)' Exec(@SqlText)
Exec(@CountSql)End
GO
(
@SQL nVARCHAR(4000), --不带排序语句的SQL语句
@Page int, --页码
@RecsPerPage int, --每页容纳的记录数
@ID VARCHAR(255), --需要排序的不重复的ID号
@Sort VARCHAR(255) --排序字段及规则
)
ASDECLARE @Str nVARCHAR(4000)SET @Str='SELECT TOP '+CAST(@RecsPerPage AS VARCHAR(20))+' * FROM '+@SQL+' T WHERE T.'+@ID+' NOT IN
(SELECT TOP '+CAST((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+' '+@ID+' FROM '+@SQL+' T9 ORDER BY '+@Sort+') ORDER BY '+@SortPRINT @StrEXEC sp_ExecuteSql @Str
GO