求百万级数据分页方法思路＆方法？

数据库情况如下
每个月业务数据都有单独的数据库存储；就是有相同12个数据库。
eg:要处理的表－－transaction(每个月都有该表)
为了方便用了一个transaction_view视图合并了12个月的transaction数据（也有些计算列）。
数据量每天有10000多，一个月30万，一年400万左右。
报表－－transaction清单
问题
1.没有用分页处理时，日报，月报都还能处理，到年报时出现内存溢出错误。（可以加内存，但这总不是解决问题的办法巴）
2.用分页处理，用了几种存储过程方法速度都很慢，也许是多数据库的原因巴，特别是排序后再分页更慢（排序字段是用户可选择的）。年表第一页要5分钟！！
希望提供各种优化建议＆思路。多数据库的结构是不可能改变的，这方面的建议就免了

解决方案 »

免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货

问题
1.没有用分页处理时，日报，月报都还能处理，到年报时出现内存溢出错误。（可以加内存，但这总不是解决问题的办法巴）
2.用分页处理，用了几种存储过程方法速度都很慢，也许是多数据库的原因巴，特别是排序后再分页更慢（排序字段是用户可选择的）。年表第一页要5分钟！！1.应该不是加内存的事儿，因为如果是内存的事儿，那加大虚拟内存就可以解决，只是速度会更慢而不至于出错。有可能是数据量太大而出现了sql server处理不了而产生了内部错误造成的，你可以查询相应的错误代码来判断，如果真如我所说是sql server内部错误造成的，可以分批处理数据。
2.如果多个数据库在一个服务器实例上，就不会是多个数据库连接的问题，因为多个数据库如果出现查询速度慢很有可能是连接费时。如果排除这一点，一般都能用加索引来解决问题。
参考：
千万级数据库高速分页显示（讨论）/*
  经测试，在 14483461 条记录中查询第 100000 页，每页 10 条记录按升序和降序第一次时间均为 0.47 秒，第二次时间均为 0.43 秒，测试语法如下：
  exec GetRecordFromPage news,newsid,10,100000
  news 为表名, newsid 为关键字段, 使用时请先对 newsid 建立索引。
*//*
  函数名称: GetRecordFromPage
  函数功能: 获取指定页的数据
  参数说明: @tblName      包含数据的表名
           @fldName      关键字段名
           @PageSize     每页记录数
           @PageIndex    要获取的页码
           @OrderType    排序类型, 0 - 升序, 1 - 降序
           @strWhere     查询条件 (注意: 不要加 where)
  */
CREATE PROCEDURE GetRecordFromPage
    @tblName      varchar(255),       -- 表名
    @fldName      varchar(255),       -- 字段名
    @PageSize     int = 10,           -- 页尺寸
    @PageIndex    int = 1,            -- 页码
    @OrderType    bit = 0,            -- 设置排序类型, 非 0 值则降序
    @strWhere     varchar(2000) = ''  -- 查询条件 (注意: 不要加 where)
ASdeclare @strSQL   varchar(6000)       -- 主语句
declare @strTmp   varchar(1000)       -- 临时变量
declare @strOrder varchar(500)        -- 排序类型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"
endset @strSQL = "select top " + str(@PageSize) + " * from ["
    + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["
    + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
    + @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"
    + @strOrderif @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 + " " + @strOrderif @PageIndex = 1
begin
    set @strTmp = ""
    if @strWhere != ''
        set @strTmp = " where (" + @strWhere + ")"    set @strSQL = "select top " + str(@PageSize) + " * from ["
        + @tblName + "]" + @strTmp + " " + @strOrder
endexec (@strSQL)GO
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
改一下，看看这样是不是更好一点？
/*
  函数名称: GetRecordFromPage
  函数功能: 获取指定页的数据
  参数说明:   @tblName        包含数据的表名
             @fldName        关键字段名
              @PageSize      每页记录数
             @PageIndex    要获取的页码
              @IsCount         是否要取得记录数
             @OrderType    排序类型, 0 - 升序, 1 - 降序
             @strWhere      查询条件 (注意: 不要加 where)
*/
CREATE  PROCEDURE pGO_GetRecordFromPage
    @tblName      varchar(255),       -- 表名
    @fldName      varchar(255),       -- 字段名
    @PageSize     int = 10,           -- 页尺寸
    @PageIndex    int = 1,            -- 页码
    @IsCount      bit = 0,            -- 返回记录总数, 非 0 值则返回
    @OrderType    bit = 0,            -- 设置排序类型, 非 0 值则降序
    @strWhere     varchar(1000) = ''  -- 查询条件 (注意: 不要加 where)
ASdeclare @strSQL   varchar(6000)       -- 主语句
declare @strTmp   varchar(500)        -- 临时变量
declare @strOrder varchar(400)        -- 排序类型-- 如果是查询记录总数，直接使用Count(0)函数
if @IsCount != 0
begin
  if @strWhere != ''
   set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere
  else
   set @strSQL = 'select count(*) as Total from [' + @tblName + '] '
end
--如果是想查询记录，则
else
begin
  if @PageIndex = 1
   begin
        set @strTmp = ''
        if @strWhere != ''
             set @strTmp = ' where ' + @strWhere        set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
             + @tblName + ']' + @strTmp + ' ' + @strOrder
   end
  else
   begin
    --如果是降序查询……
    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    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
    else
     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
   end
endexec (@strSQL)
GO
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
我也来一下，这个可以实现多表查询。 -- =============================================
-- 数据分页的存储过程
-- 记录号在 TempIDKey_Num 字段中
-- 调用的例子：表示从结果中第3行开始的5条记录。
-- T-SQL：EXECUTE proTest N'select top 100 percent * from orders', 3,5
-- ASP.NET(C#)：
-- <%@ Page Language="C#" %>
-- <%@ Import Namespace="System.Data" %>
-- <%@ Import Namespace="System.Data.SqlClient" %>
-- <Script Runat="Server">
-- void Page_Load( Object s, EventArgs e )
-- {
-- SqlConnection myConnection;
-- SqlCommand myCommand;
-- myConnection = new SqlConnection( "Server=(local);uid=sa;pwd=11;Database=Northwind" );
-- myCommand = new SqlCommand( "proTest", myConnection );
-- myCommand.CommandType = CommandType.StoredProcedure;
-- myCommand.Parameters.Add("@strSql","Select top 30 * from orders");
-- myCommand.Parameters.Add("@startRow",10);
-- myCommand.Parameters.Add("@maxRows",15);
-- myConnection.Open();
-- myDataGrid.DataSource = myCommand.ExecuteReader();
-- myDataGrid.DataBind();
-- myConnection.Close();
-- }
-- </Script>
-- <html><head><title>DataGrid</title></head><body>
-- <form Runat="Server">
-- <asp:DataGrid id="myDataGrid" Runat="Server"/>
-- </form>
-- </body></html>
-- =============================================
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'proTest'
AND type = 'P')
DROP PROCEDURE proTest
GO
CREATE PROCEDURE proTest
@strSql as nvarchar(2000) = null, --要查询语句如 Select top 30 * from orders
@startRow as int = null, --从其开始的从零开始的记录号
@maxRows as int = null --要检索的最大记录数
AS
DECLARE @stopRow as int
set @stopRow = @startRow + @maxRows set @strSql = N' Select top ' + CAST(@StopRow as nvarchar(9)) + '*, IDENTITY(int,1,1) AS TempIDKey_Num '
+ ' INTO #New_Table '
+ ' FROM( ' + @strSql + ') A '
+ ' Select * From #New_Table Where TempIDKey_Num>=' + CAST(@StartRow as nvarchar(9))
+ ' DROP TABLE #New_Table '
execute sp_executesql @strSql GO
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
这个效率不错。
但是我目前碰到了一些问题，不知道大家有什么好的解决办法。1、当排序字段为一个以上时，如有如下查询语句：
     select id, a,b,c from tb where a='aa' order by a,b
     即@fldName 为'a,b'时，出错：
  列名 'a,b' 无效。2、当某个表没有ID字段，只有复合主键（如a和b）时，也通不过
回：geniusli(纠级天使) 我这里的内存溢出是asp.net服务报错，sql应该没有，我再查询分析器中能查询出结果
TO z_yy(歪歪) 谢谢了，不过你的方法我用过了速度不是很快，也许是我的视图有问题巴。其中前两个sp我认为有些问题，首先只能单个排序，再是用min，max来判断我认为不是很严谨，如果排序字段的值是唯一的那还行，二我这里排序的值不可能唯一，而且有可能重复比较多（呵呵，我这里ID 都不唯一，多表的缘故）。全局临时表分页我正在尝试，不过排序后的速度不是很快，可能没有索引和计算列比较复杂巴
我是这样处理的
select [ID_E82212F1-2F0B-412B-80DE-045D5C03BDC4]=identity(int,0,1), F1,F2,F3,F4,F5,F6,F7 ,Time1,Time2,Time3
        into #tb from (
select F1,F2,F3,F4,F5,F6,F7 ,Time1,Time2,Time3
from [January].dbo.Transaction
union all
select F1,F2,F3,F4,F5,F6,F7 ,Time1,Time2,Time3
from [February].dbo.Transaction
union all
select F1,F2,F3,F4,F5,F6,F7 ,Time1,Time2,Time3
from [March].dbo.Transaction) a order by StampTime
    select  F1,F2,F3,F4,F5,F6,F7 ,Time1,Time2,Time3 from #tb where [ID_E82212F1-2F0B-412B-80DE-045D5C03BDC4] between 25 and 49
三个月数据，第二页用了40秒－1分钟
Time1,Time2,Time3 再对于的数据库加了索引，好像效率很低，这里我还没有用计算列，如果这样做的话年报的每翻一页要4－5分钟，开玩笑么，各位看看还有没有可以优化的
用临时表缓存，每天定时对原始表作一次统计，放在临时表里，再从临时表中作月报，年报统计。这样比仅是优化SQL的效率更高
兄弟，相信这个对你有帮助
http://www.17build.com/experience/sqlserver/sqlpageproc.htm
to Guizhi 我这里是明细类报表不是集计类的，
to bJiao  方法以前用过了，速度不行，是多数据库的原因
不过还是谢谢两位
如果是SQLSERVER，可以用临时表进行分页。/**
** 分页存储过程
** author : dgs
** date : 2005-10-01
**
** 传入的参数有：
**
** 备注：创建临时表
**/

if exists (select 1
          from sysobjects
          where name = 'Proc_PageableQuery'
          and type = 'P')
   drop procedure Proc_PageableQuery
go
create procedure Proc_PageableQuery
@QueryStr nvarchar(4000), -- 查询语句
@BeginRow varchar(8), -- 页起始的位置
@PageSize varchar(8) -- 每页行数as
declare @tmpSQL nvarchar(4000) -- 查询语句
declare @StrSQL nvarchar(4000) -- 查询语句begin
-- 初始化数据库
SET NOCOUNT ON;

-- 去掉左边空格，和 select
select @tmpSQL = lower(ltrim(@QueryStr));
select @tmpSQL = substring(@tmpSQL, len('select ') + 1, len(@tmpSQL));

-- 如果显示第一页，可以直接用top来完成
if @BeginRow = '0'
begin
select @StrSQL = 'select top ' + @PageSize + ' ' + @tmpSQL;
exec(@StrSQL);
return ;
end;

-- 先创建临时表
select @StrSQL = 'select identity(int, 1, 1) as id_PageRowID, * into #tmpPageTable from ( select top 100 percent '
+ @tmpSQL + ') table_TempPage ;';

-- 查询
select @StrSQL = @StrSQL + ' select top ' + @PageSize + ' * from #tmpPageTable where id_PageRowID between ('
+ @BeginRow + ' + 1) and (' + @BeginRow + ' + ' + @PageSize + '); ';

-- 除去临时表
select @strSQL = @strSQL + ' drop table #tmpPageTable;';

-- 运行
exec(@StrSQL);

SET NOCOUNT OFF;

end
go
我用自己的手提测过速度，PM1.6G, 768MB内存查询语句为： select * from ttttt数据量为 100 万时，查询速度为：3717.8 ms
数据量为 200 万时，查询速度为：28338.3 ms
数据量为 400 万时，查询速度为：50857.7 ms查询语句为： select fields from ttttt数据量为 100 万时，查询速度为：1784 ms
数据量为 200 万时，查询速度为：3678.4 ms
数据量为 400 万时，查询速度为：9830.4 ms
同意Guizhi(给头奖彩票砸晕的那个) 的方法把每月的报表数字存在一张表里，统计的时候月份数据只要从那张表里取就可以了，不过要注意一些细节问题，比如单据隔月作废之类的处理