Question

如果在数据库中有大数据量，而我们用分页存储过程，怎么样才能效率高

Answer 1

如果你每页显示10条记录那么你就每次查询10条记录 在Oracle数据库中:方法一:
SELECT *
FROM databasetest t1
WHERE (SELECT count(*) FROM databasetest t2 WHERE t2.id < t1.id) >= 11 AND
(SELECT count(*) FROM databasetest t2 WHERE t2.id < t1.id) < 20
花费时间: 62.156秒方法二:
select *
from (select rownum as num,id,test1,test2 from databasetest a where rownum < 20)
where num > 11;
花费时间: 0.094秒
解释：rownum意为读取行号，首先读取小于20行的记录，然后在这些记录中读取行号大于10的记录，行号是整个表的同一分配。

方法三:
(select * from databasetest where rownum<=20) minus (select * from databasetest where rownum<11)
花费时间: 0.11秒

在SQL Server中方法一：
SELECT *
FROM databasetest t1
WHERE (SELECT count(*) FROM databasetest t2 WHERE t2.id < t1.id) >= 11 AND
(SELECT count(*) FROM databasetest t2 WHERE t2.id < t1.id) < 20
花费时间：id未加索引为18秒，加上索引后为7秒

方法二:
select top 10 * from databasetest where id not in (select top 10 id from databasetest);
花费时间: 小于1秒
解释：首先查找整个表的前10个记录，然后除前10个记录的其它记录中找前面10个记录。可理解为：在表中，除了前面10个记录，找出靠前的10个记录总结：
在Oracle最佳查询为第二种方法,使用rownum函数，在SQL Server中最佳查询为第二种方法，使用Top函数。
其中第一种方法使用于任何数据库。为了减少网络通信，同时又提高查询速度，可以使用缓冲。即一次查询足够多的记录，保存在缓存中，传给客户，当客户需要查看指定记录时，从缓存中取出数据。具体实现方案为：假如每页10条记录，如果查看第5页记录，则一次查找的40-69共三十条记录,存入缓存。当选择上一页，下一页时从缓存中读出数据，当查找第7页的数据时，再查找6－8页数据。减少了与数据库的网络通信，同时又提高了效率。

SQL语句实现数据分页（SQLServer）
SQLServer的分页依靠的是top这个属性。1.分页方案一：(利用Not In和SELECT TOP分页)SELECT TOP 页大小 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id))
ORDER BY ID2.分页方案二：(利用ID大于多少和SELECT TOP分页）SELECT TOP 页大小 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id) AS T))
ORDER BY ID常用的是方案1.这个不需要多说！方案2有局限性。

Answer 2

--------------------------------
--关于分页储存的效率问题
--5个存储过程都是采用不同的方式
--------------------------------
------------------------------------------
--利用select top 和select not in进行分页--
------------------------------------------
create procedure 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 procedure 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 procedure 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 procedure 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 procedure 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中间变量966ms970ms960ms945ms933ms54row_number0ms0ms34ms365ms710ms24临时表780ms796ms798ms780ms805ms4正好我正在研究这个问题 给大家分享

Answer 3

不行