『壹』 如果在数据库中有大数据量，而我们用分页存储过程，怎么样才能效率高
--------------------------------
--关于分页储存的效率问题
--5个存储过程都是采用不同的方式
--------------------------------
------------------------------------------
--利用select top 和select not in进行分页--
------------------------------------------
create procere proc_paged_with_notin --利用select top and select not in
(
@pageIndex int, --页索引
@pageSize int --每页记录数
)
as
begin
set nocount on;
declare @timediff datetime --耗时
declare @sql nvarchar(500)
select @timediff=Getdate()
set @sql='select top '+str(@pageSize)+' * from tb_TestTable where(ID not in(select top '+str(@pageSize*@pageIndex)+' id from tb_TestTable order by ID ASC)) order by ID'
execute(@sql) --因select top后不支技直接接参数，所以写成了字符串@sql
select datediff(ms,@timediff,GetDate()) as 耗时
set nocount off;
endexec proc_paged_with_notin 10000,10
--------------------------------------
--利用select top 和 select max(列键)--
--------------------------------------
create procere proc_paged_with_selectMax --利用select top and select max(列)
(
@pageIndex int, --页索引
@pageSize int --页记录数
)
as
begin
set nocount on;
declare @timediff datetime
declare @sql nvarchar(500)
select @timediff=Getdate()
set @sql='select top '+str(@pageSize)+' * From tb_TestTable where(ID>(select max(id) From (select top '+str(@pageSize*@pageIndex)+' id From tb_TestTable order by ID) as TempTable)) order by ID'
execute(@sql)
select datediff(ms,@timediff,GetDate()) as 耗时
set nocount off;
end--------------------------------------------------------
--利用select top和中间变量--此方法因网上有人说效果最佳--
--------------------------------------------------------
create procere proc_paged_with_Midvar --利用ID>最大ID值和中间变量
(
@pageIndex int,
@pageSize int
)
as
declare @count int
declare @ID int
declare @timediff datetime
declare @sql nvarchar(500)
begin
set nocount on;
select @count=0,@ID=0,@timediff=getdate()
select @count=@count+1,@ID=case when @count<=@pageSize*@pageIndex then ID else @ID end from tb_testTable order by id
set @sql='select top '+str(@pageSize)+' * from tb_testTable where ID>'+str(@ID)
execute(@sql)
select datediff(ms,@timediff,getdate()) as 耗时
set nocount off;
end
---------------------------------------------------------------------------------------
--利用Row_number() 此方法为SQL server 2005中新的方法,利用Row_number()给数据行加上索引--
---------------------------------------------------------------------------------------
create procere proc_paged_with_Rownumber --利用SQL 2005中的Row_number()
(
@pageIndex int,
@pageSize int
)
as
declare @timediff datetime
begin
set nocount on;
select @timediff=getdate()
select * from (select *,Row_number() over(order by ID asc) as IDRank from tb_testTable) as IDWithRowNumber where IDRank>@pageSize*@pageIndex and IDRank<@pageSize*(@pageIndex+1)
select datediff(ms,@timediff,getdate()) as 耗时
set nocount off;
end
--------------------------
--利用临时表及Row_number--
--------------------------
create procere proc_CTE --利用临时表及Row_number
(
@pageIndex int, --页索引
@pageSize int --页记录数
)
as
set nocount on;
declare @ctestr nvarchar(400)
declare @strSql nvarchar(400)
declare @datediff datetime
begin
select @datediff=GetDate()
set @ctestr='with Table_CTE as
(select ceiling((Row_number() over(order by ID ASC))/'+str(@pageSize)+') as page_num,* from tb_TestTable)';
set @strSql=@ctestr+' select * From Table_CTE where page_num='+str(@pageIndex)
end
begin
execute sp_executesql @strSql
select datediff(ms,@datediff,GetDate())
set nocount off;
end
我们分别在每页10条数据的情况下在第2页，第1000页，第10000页，第100000页，第199999页进行测试，耗时单位：ms 每页测试5次取其平均值 存过第2页耗时第1000页耗时第10000页耗时第100000页耗时第199999页耗时效率排行1用not in0ms16ms47ms475ms953ms32用select max5ms16ms35ms325ms623ms13中间变量_number0ms0ms34ms365ms710ms24临时表780ms796ms798ms780ms805ms4正好我正在研究这个问题 给大家分享
『贰』 （问题解决再追加100分）sql server存储过程实现查询数据条数过大，分页查询怎么实现
按说5-8w这样数量级的数据没有问题，写入Excel是布比较耗性能，主要还是要通过优化写入Excel的代码效率上去考虑。你可以考虑利用分批查询写入的方式来避免一次写太多的数据到Excel：将你的查询结果分段，比方你的语句中能不能用时间来认为分段，每次返回部分结果。
回到你的问题，对大数据量查询的解决方案有以下两种：
（1）、将全部数据先查询到内存中，然后在内存中进行分页，这种方式对内存占用较大，必须限制一次查询的数据量。
（2）、采用存储过程在数据库中进行分页，这种方式对数据库的依赖较大，不同的数据库实现机制不通，并且查询效率不够理想。以上两种方式对用户来说都不够友好。
2．解决思路
通过在待查询的数据库表上增加一个用于查询的自增长字段，然后采用该字段进行分页查询，可以很好地解决这个问题。下面举例说明这种分页查询方案。
（1）、在待查询的表格上增加一个long型的自增长列，取名为“queryId”,mssql、sybase直接支持自增长字段，oracle可以用sequence和trigger来实现。然后在该列上加上一个索引。
添加queryId列的语句如下：
Mssql: [QUERYID] [bigint] IDENTITY (1, 1)
Sybase: QUERYID numeric(19) identity
Oracle:
CREATE SEQUENCE queryId_S
INCREMENT BY 1
START WITH 1
MAXVALUE 999999999999999 MINVALUE 1
CYCLE
CACHE 20
ORDER;
CREATE OR REPLACE TRIGGER queryId_T BEFORE INSERT
ON "test_table"
FOR EACH ROW
BEGIN
select queryId_S.nextval into :new.queryId from al;
END;
（2）、在查询第一页时，先按照大小顺序的倒序查出所有的queryId，
语句如下：select queryId from test_table where + 查询条件 +order by queryId desc 。
因为只是查询queryId字段，即使表格中的数据量很大，该查询也会很快得到结果。然后将得到的queryId保存在应用服务器的一个数组中。
（3）、用户在客户端进行翻页操作时，客户端将待查询的页号作为参数传递给应用服务器，服务器通过页号和queyId数组算出待查询的queyId最大和最小值，然后进行查询。
算出queyId最大和最小值的算法如下,其中page为待查询的页号，pageSize为每页的大小，queryIds为第二步生成的queryId数组：
int startRow = (page - 1) * pageSize
int endRow = page * pageSize - 1;
if (endRow >=queryIds.length)
{
endRow = this.queryIds.length - 1;
}
long startId =queryIds[startRow];
long endId =queryIds[endRow];
查询语句如下：
String sql = "select * from test_table" + 查询条件 + "(queryId <= " + startId + " and queryId >= " + endId + ")";
3．效果评价
该分页查询方法对所有数据库都适用，对应用服务器、数据库服务器、查询客户端的cpu和内存占用都较低，查询速度较快，是一个较为理想的分页查询实现方案。经过测试，查询4百万条数据，可以在3分钟内显示出首页数据，以后每一次翻页操作基本在2秒以内。内存和cpu占用无明显增长。
以上也仅仅是分页查询结果查看的问题，你需要写入到Excel的话还需要考虑Excel写入代码的执行效率，这部分是很值得研究的。
『叁』 sqlserver2000 如何提高分页查询大数据量的效率
sqlserver2005及以上的版本有row_number()函数可以高效分页，sqlserver2000的话只能看算法了
『肆』 c# 如何快速处理大数据量得查询及显示
分页查询
每次只查一页数量的数据（如20条）
还要查一下总记录数，这样可以计算出页回数，然答后点击不同的页查询不同的记录，但每次只查一页数量的数据（如20条）
例如
select count(*) form tab 记录数 通过这个进行分页布局
sekect * from tab where rownum>=根据页数和每页记录数计算 and rownum<根据页数和每页记录数计算
『伍』 Oracle 数据量非常大（上亿）时，使用存储过程中的游标返回分页查询的10条记录非常耗时，请问如何优化
With New_Table As
(Select Logid, No, Opttime, Rownum As Row_Num
From T_Tab_Log
Where Part_Date = ?)
Select Logid, No
From New_Table t
Where T.Row_Num <= ? And T.Row_Num >= ?
Order By Opttime Desc;