高分悬赏高手近，大数据量分页实战，两台服务器测试

wdx2008 2008-06-06 01:32:58

网站数据现在有大概8万条数据分页，现在变的非常慢，甚至超时死掉
希望大家能给出较好的解决方案，将速度提上去为王！
我把存储过程贴出来，大家帮看看哈

分页列表：
http://www.zhaoyizhao.net/infolist.aspx?bid=16 （第一台服务器）
http://zhaoyizhao.net/infolist.aspx?bid=16 （第二台服务器）
---------
www.zhaoyizhao.net 和zhaoyizhao.net我解析到了两台服务器测试，调用相同数据库（肯定数据库服务器速度正常）

ALTER PROCEDURE [dbo].[mypager]

/*

***************************************************************

***************************************************************

参数说明:

1.Tables :表名称,视图

2.PrimaryKey :主关键字

3.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc

4.CurrentPage :当前页码

5.PageSize :分页尺寸

6.Filter :过滤语句，不带Where 

7.Group :Group语句,不带Group By

***************************************************************/

(

@Tables varchar(1000),

@PrimaryKey varchar(100),

@Sort varchar(200) = NULL,

@CurrentPage int = 1,

@PageSize int = 10,

@Fields varchar(1000) = '*',

@Filter varchar(1000) = NULL,

@Group varchar(1000) = NULL,

@RecordCount int OUTPUT 

)

AS

/*默认排序*/

IF @Sort IS NULL OR @Sort = ''

SET @Sort = @PrimaryKey

DECLARE @SortTable varchar(100)

DECLARE @SortName varchar(100)

DECLARE @strSortColumn varchar(200)

DECLARE @operator char(2)

DECLARE @type varchar(100)

DECLARE @prec int

declare @str NVarchar(600) 



/*设定排序语句.*/

IF CHARINDEX('DESC',@Sort)>0

BEGIN

SET @strSortColumn = REPLACE(@Sort, 'DESC', '')

SET @operator = '<='

END

ELSE

BEGIN

IF CHARINDEX('ASC', @Sort) = 0

SET @strSortColumn = REPLACE(@Sort, 'ASC', '')

SET @operator = '>='

END

IF CHARINDEX('.', @strSortColumn) > 0

BEGIN

SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))

SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))

END

ELSE

BEGIN

SET @SortTable = @Tables

SET @SortName = @strSortColumn

END

SELECT @type=t.name, @prec=c.prec

FROM sysobjects o 

JOIN syscolumns c on o.id=c.id

JOIN systypes t on c.xusertype=t.xusertype

WHERE o.name = @SortTable AND c.name = @SortName

IF CHARINDEX('char', @type) > 0

SET @type = @type + '(' + CAST(@prec AS varchar) + ')'

DECLARE @strPageSize varchar(50)

DECLARE @strStartRow varchar(50)

DECLARE @strFilter varchar(1000)

DECLARE @strSimpleFilter varchar(1000)

DECLARE @strGroup varchar(1000)

/*默认当前页*/

IF @CurrentPage < 1

SET @CurrentPage = 1

/*设置分页参数.*/

SET @strPageSize = CAST(@PageSize AS varchar(50))

SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))

/*筛选以及分组语句.*/

IF @Filter IS NOT NULL AND @Filter != ''

BEGIN

SET @strFilter = ' WHERE ' + @Filter + ' '

SET @strSimpleFilter = ' AND ' + @Filter + ' '

END

ELSE

BEGIN

SET @strSimpleFilter = ''

SET @strFilter = ''

END

IF @Group IS NOT NULL AND @Group != ''

SET @strGroup = ' GROUP BY ' + @Group + ' '

ELSE

SET @strGroup = ''

/*执行查询语句*/



select @str=N'select @recordcount=count(*) from '+@Tables+ @strFilter

Execute sp_executesql @str,N'@recordcount int OUTPUT',@recordcount=@recordcount OUTPUT



EXEC(

'

DECLARE @SortColumn ' + @type + '

SET ROWCOUNT ' + @strStartRow + '

SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '

SET ROWCOUNT ' + @strPageSize + '

SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '

'

)

...全文

205 30 打赏收藏转发到动态举报

写回复

用AI写文章

30 条回复

切换为时间正序

请发表友善的回复…

发表回复

winner2050 2008-06-07

打赏
举报

-_-！分个页都大费周章。

zhangpf_t 2008-06-06

打赏
举报

sql2005有row_num

流梓 2008-06-06

打赏
举报

楼主，主要是看数据库索引！其它的数据量大，程序是一方在，后台的数据库设计、维护非常重要

leer168 2008-06-06

打赏
举报

mark

rainxy2000 2008-06-06

打赏
举报

有这么麻烦吗?
WITH OrderedOrders AS
(SELECT *,
ROW_NUMBER() OVER (order by [id])as RowNumber　　--id是用来排序的列
FROM table_info ) --table_info是表名
SELECT *
FROM OrderedOrders
WHERE RowNumber between 50 and 60;

在windows server 2003, sql server 2005 CTP,P4 2.66GHZ,1GB 内存下测试，执行时间0秒，表中数据量230万

接下来大批量的数据查询性能瓶颈就在count了，不知道sql server 2005在这方面有没有什么改进.

附另一种方法:
SELECT *
FROM (select *,ROW_NUMBER() Over(order by id) as rowNum from table_info ) as myTable
where rowNum between 50 and 60;

Atai-Lu 2008-06-06

打赏
举报

http://topic.csdn.net/u/20080507/19/82739f4b-ecfe-429c-99b1-6278cb21ef5a.html
这个帖子你看看，也许你能找到一些灵感

WDFrog 2008-06-06

打赏
举报

看看动易7.0 (.net2.0)官方的分页存储过程,因为能任意指定排序字段,性能在 10万级别时接近那个基于那个max id 的,比使用临时表的性能要好,

http://www.cnblogs.com/wdfrog/archive/2008/02/02/1062263.html

wdx2008 2008-06-06

打赏
举报

经测试，把内容去掉，前台也只显示标题速度还是很慢，12秒！
哎

wzy_love_sly 2008-06-06

打赏
举报

主要是索引吧，3秒都有问题

wdx2008 2008-06-06

打赏
举报

找出来几个主要原因：
1.给查询的addtime添加索引，查询效率明显提升！
后台8万多条记录翻页从原来的近20秒缩短到差不多3秒！
后台现在分页非常快可以说。
2.前台因为提取了内容简介，所以还是比较慢。text字段无法建立索引所以前台还是非常慢，大家出出招亚？
希望这个帖子彻底解决普遍遇到的大数据量分页问题

Atai-Lu 2008-06-06

打赏
举报



declare @pagesize int

declare @pageindex int



set @pagesize = 100

set @pageindex=11

--



declare @strSQL varchar(5000)

--准备分页

declare @maxpage int --总页数

declare @datacount int --总记录数

--set @maxpage=0

set @datacount=(select count(*) from [dict])



if(@datacount%@pagesize)=0

	begin

		set @maxpage=(@datacount/@pagesize)

	end

else

	begin

		set @maxpage=(@datacount/@pagesize)+1

	end



if @pageindex = 1

	begin

	SET @strSQL = 'select top '+str(@pagesize)+' *,'+str(@maxpage)+' as MaxPage,

		'+str(@datacount)+' as DataCount

		from [dict]'

 	end



else

	 begin

	SET @strSQL = 'select top '+str(@pagesize)+' *,'+str(@maxpage)+' as MaxPage,

		'+str(@datacount)+' as DataCount

		from [dict] where [id] not in(select top '+str((@pageindex-1)*@pagesize)+' [id]

		from [dict])'

 	end

	EXEC(@strSQL)

数据数：157202条，速度还比较快，你可以参考下，我这里157202条数据，查询分析器里显示的执行时间是0:00:00

elvis_gao 2008-06-06

打赏
举报

以前写的，一直也没用过，大家看看效率怎么样

--=============================================

-- Author:	<Elvis gao>

-- Create date: <2007-08-31>

-- Description:	< 分页存储过程，仅适用于Sql2005（后加入返回页面数）>

-- =============================================



Create PROCEDURE  PageProc_GetRecordByPage

    @tblName      varchar(255),       -- 表名

    @fldName      varchar(255),       -- 主键字段名

    @PageSize     int = 10,           -- 页尺寸

    @PageIndex    int = 1,            -- 页码

    @IsReCount    bit = 0,            -- 返回记录总数, 非0 值则返回

    @IsRePageCount  bit=0,            -- 返回页面总数, 非0 值则返回

    @OrderType    bit = 0,            -- 设置排序类型, 非0 值则降序

    @strWhere     varchar(1000) = ''  -- 查询条件(注意: 不要加where)

AS



declare @strSQL   varchar(6000)       -- 主语句

declare @strTmp   varchar(100)        -- 临时变量

declare @strOrder varchar(400)        -- 排序类型



if @OrderType != 0

begin

    set @strTmp = '<(select min'

    set @strOrder = ' order by [' + @fldName +'] desc  '

end

else

begin

    set @strTmp = '>(select max'

    set @strOrder = ' order by [' + @fldName +'] asc  '

end



set @strSQL = 'select top ' + str(@PageSize) + ' * from ['

    + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['

    + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['

    + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'

    + @strOrder



if @strWhere != ''

    set @strSQL = 'select top ' + str(@PageSize) + ' * from ['

        + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['

        + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['

        + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '

        + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder



if @PageIndex = 1

begin

    set @strTmp =''

    if @strWhere != ''

        set @strTmp = ' where ' + @strWhere



    set @strSQL = 'select top ' + str(@PageSize) + ' * from ['

        + @tblName + ']' + @strTmp + ' ' + @strOrder

end



if @IsRePageCount=1

begin 

    declare @Der varchar(1000)

    set @Der = ' declare @Count as int 

				 set @Count = (select count(*) from ' + @tblName + ' where ' +  @strWhere  + ') '

    set @Der = @Der + ' declare @PageCount decimal(18,2) '+' set @PageCount = cast(convert(decimal(18,2),@Count)/convert(decimal(18,2),' + convert(nvarchar(10),@PageSize) + ') as decimal(18,2))   '

    set @Der = @Der + ' declare @PageFloor int ' + ' set @PageFloor=floor(@PageCount) ' + ' if cast(@PageFloor as decimal(18,2)) < @PageCount   select @PageFloor+1 as PageCount  else  select @PageFloor as PageCount '

 set @strSQL = @strSQL + @Der

end



if @IsReCount != 0

    set @strSQL = @strSQL + ' select count(*) as Total from [' + @tblName + ']'+' where  ' + @strWhere



exec (@strSQL)

wdx2008 2008-06-06

打赏
举报

有做过已在运行的大数据量分页网站的朋友吗？
指教一二哈

wdx2008 2008-06-06

打赏
举报

我现在用的分页控件是：WTS
有人对比不过WTS和AspNetPager的效率吗？

Atai-Lu 2008-06-06

打赏
举报

到查询分析器里执行下存储过程，然后选择查询--显示估计的执行计划，看看是哪个步骤消耗的资源最多，然后想办法优化一下

wdx2008 2008-06-06

打赏
举报

把类目去掉，只显示列表内容速度还是很慢
后台只有信息列表，反应速度和前台差不多
初步看来应该是存储过程的问题

wdx2008 2008-06-06

打赏
举报

数据表比较简单，主信息表就一个INFO，主键ID是索引
这个列表页面主要调用上面的类别表+下面的信息表
哎，如此之慢.......

lovehongyun 2008-06-06

打赏
举报

[Quote=引用 11 楼 panhaichun 的回复:]
我搞不懂为什么一条SQL语句就能搞定的分页得写个存储过程

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= ?
)
WHERE RN > ?
[/Quote]

sql server里没有rownum,
oracle里有
sql 2005里提供rownum函数

panhaichun 2008-06-06