求SQL2005高性能存储过程分页代码,该怎么处理

求SQL2005高性能存储过程分页代码
求sql2005高性能存储过程分页代码及调用代码
前辈们
小弟刚入行请写注释！
谢谢！

数据大概在6-8万条左右峰值在 14万左右谢谢啦

ASP.NET

------解决方案--------------------
才14W的数据有什么好怕的！

SQL code


CREATE PROCEDURE  dbo.f_page
(@pagesize int,
@pageindex int,
@docount bit,
@argcode varchar(100),
@argtable varchar(100),
@order varchar(100),
@where varchar(500),
@counts  int = 1 output
) AS 


BEGIN TRANSACTION

SET NOCOUNT ON 

IF(@docount=1)  
    BEGIN
        DECLARE @sqlStr Varchar(4000) 
        SET @sqlStr='SELECT COUNT('+@argcode+') FROM '+@argtable+' WHERE ' + @where 
        EXEC(@sqlStr)
    END
ELSE  
    BEGIN 
        CREATE TABLE #indextable(id bigint identity(1,1),nid bigint)
        DECLARE @PageLowerBound int
        DECLARE @PageUpperBound int
        DECLARE @sqlStr4 nvarchar(4000) 
        DECLARE @sqlStr1 Varchar(4000) 
        DECLARE @sqlStr2 Varchar(4000) 
        DECLARE @sqlStr3 Varchar(4000) 
DECLARE @a1 varchar(100)
DECLARE @a2 varchar(100)
         SET @PageLowerBound=(@pageindex-1)*@pagesize
        SET @PageUpperBound=@PageLowerBound+@pagesize
        SET ROWCOUNT @PageUpperBound
set @a1=@PageLowerBound
set @a2=@PageUpperBound
       set @sqlstr3='AND t.id>'+@a1+' AND t.id<='+@a2
        SET @sqlStr1='INSERT INTO #indextable(nid) SELECT '+@argcode+' FROM '+@argtable+'  WHERE ' + @where  + '  ORDER BY  ' + @order 
        EXEC(@sqlStr1)
        SET @sqlstr2=' SELECT * FROM '+@argtable+',#indextable t WHERE '+@argcode+' =t.nid  '+@sqlstr3+'  ORDER BY t.id'
        SET @sqlStr4='SELECT @counts=COUNT('+@argcode+') FROM '+@argtable+' WHERE ' + @where 
        exec sp_executesql @sqlStr4,N'@counts int out ',@counts out
        Exec (@sqlstr2)
    END
SET NOCOUNT OFF


COMMIT TRANSACTION

GO

------解决方案--------------------

SQL code

ALTER PROCEDURE [dbo].[Pg_Paging]
@Tables varchar(1000), --表名,多红表是请使用 tA a inner join tB b On a.AID = b.AID
@PK varchar(100),    --主键，可以带表头 a.AID
@Sort varchar(200) = '', --排序字段
@PageNumber int = 1,    --开始页码
@PageSize int = 10,        --页大小
@Fields varchar(1000) = '*',--读取字段
@Filter varchar(1000) = NULL,--Where条件
@Group varchar(1000) = NULL,  --分组
@isCount bit = 0     --1    --是否获得总记录数
AS
--
--select * from GL_NEWS order by GN_UPDATE_DATE DESC
--exec Pg_Paging @Tables = 'tb_NewsInfo', @PK = 'News_ID', @Sort = 'News_ID DESC', @PageNumber = 2, @PageSize = 15,@Fields = '*', @Group = '', @isCount = 0
DECLARE @strFilter varchar(2000)
declare @sql varchar(8000)
IF @Filter IS NOT NULL AND @Filter != ''
  BEGIN
   SET @strFilter = ' WHERE ' + @Filter + ' '
  END
ELSE
  BEGIN
   SET @strFilter = ''
  END

if @isCount = 1 --只获得记录条数
    begin
        set @sql = 'SELECT  Count(*) FROM ' + @Tables + @strFilter  
    end
else
begin
if @Sort = ''
  set @Sort = @PK + ' DESC '

IF @PageNumber < 1
  SET @PageNumber = 1

if @PageNumber = 1 --第一页提高性能
begin 
  set @sql = 'select top ' + str(@PageSize) +' '+@Fields+ '  from ' + @Tables + ' ' + @strFilter + ' ORDER BY  '+ @Sort
end 
else
  begin
  /**//**//**//*Execute dynamic query*/    
   DECLARE @START_ID varchar(50)
DECLARE @END_ID varchar(50)
SET @START_ID = convert(varchar(50),(@PageNumber - 1) * @PageSize + 1)
SET @END_ID = convert(varchar(50),@PageNumber * @PageSize)
    set @sql =  ' SELECT '+@Fields+ '
   FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@Sort+') AS rownum, 
     '+@Fields+ '
      FROM '+@Tables+') AS D
   WHERE rownum BETWEEN '+@START_ID+' AND ' +@END_ID +' ORDER BY '+@Sort
  END

END
--print @sql

EXEC(@sql)

求SQL2005高性能存储过程分页代码,该怎么处理

相关推荐