如果在数据库中有大数据量,而我们用分页存储过程,怎么样才能效率高...
发布网友
发布时间:2022-05-17 10:42
我来回答
共4个回答
好二三四
时间:2022-07-12 09:43
大数据量下的分页解决方法:要看你的数据存储是用的什么数据库了。常用的有mysql,sqlserver,oracle。没种数据库进行分页的SQL语句不同。
做大数据分页都是无刷新的技术,这里我们选择ajax来实现。ajax请求地址需要你使用后台代码来实现,后台代码除了要返回数据集合还要返回数据的总数量,总页数,下一页等参数,方便选择分页的时候获取数据。
下面看一下后台代码实现,sqlserver的分页SQL:selecttop一页数量*from表名where主键notin(selecttop15主键from表名)
mysql的分页语句SQL:select*from表名where主键>10orderbydeptnoascpmitn;
热心网友
时间:2022-07-12 06:51
如果你每页显示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有局限性。
热心网友
时间:2022-07-12 08:09
--------------------------------
--关于分页储存的效率问题
--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中间变量966ms970ms960ms945ms933ms54row_number0ms0ms34ms365ms710ms24临时表780ms796ms798ms780ms805ms4正好我正在研究这个问题 给大家分享
热心网友
时间:2022-07-12 09:43
不行