基于MSSQLSQL数据库大批量数据的分块分页查询

    
mybillliu 05-05-09

/*
create table tmp
(
gdate datetime default getdate(),
gid int
)

*/
/*
declare @i int

set @i = 1

while @i<1000000
begin

INSERT tmp(gid) values(@i)

print '-------' + cast(@i as char(10))
set @i = @i + 1

end


select count(*) from tmp
*/
-------------------------------
--@page 需要取出第几页
--@pageSize 用于分页时,每页显示的记录数目
--@blockSize 在大批量数据库中不必每次取pageSize条记录,每次取一批数据,减轻数据库负担
declare @page int,@pageSize int,@sql char(300),
@blockSize int,@d datetime
set @page = 100000
set @blockSize = 400
set @pageSize = 20



if @page =1
begin
set @sql = 'select top ' + cast(@blockSize as varchar) +
' * from tmp order by gdate desc '
end
else
begin
set @sql =
'select top '
+
cast(@blockSize as varchar)
+
' *
from tmp
where gdate<
(select min(gdate) from
(
select top '
+
cast((@page - 1)* @pageSize as varchar)
+
' gdate from tmp order by gdate desc
) as t
)
order by gdate desc'
end

print @sql

set @d=getdate()

exec(@sql)

select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())


--select * from t where gid<50000 order by gdate desc

用户先根据上面的代码新建一个表tmp
我在实际应用中为测试效率问题,因此插入了1KW条记录进行效率考验
根据实际测试,效率还非常高,用户在实际应用中可参考或应用上面的代码。不过没有考虑到并发用户访问数据库情况。
原理:主要是分块取数据,上面代码中我每次取400条记录,根据时间排序取数据(最近原则),页面大小和页用户可指定。
关于在不同环境下测试,耗时就不必贴出了。
有疑问或者有其它更好的建议,请跟贴。

4
mybillliu
2005-05-09 17:19

又在标题中多写了个“SQL”,唉!

mybillliu
2005-05-09 17:26

对于其它数据下分块分页查询,根据相同原理写出就行。
在实际应用中,用户可建立相关索引可提高数据库查询速度。

用户可试用其它查询方法比较效率如:
用top和not in 结合方式进行分块分矾查询也是可以,但not in这种方式实践证明会降低查询效率,不如用top和min或者MAX方式结合效率高,用户可实践试试,不过得在大批量数据下试验才有意义。

banq
2005-05-10 17:15

切勿针对具体数据库实现优化,要做成通用的中间件,参考Jdon框架的批量查询优化。

mybillliu
2005-05-11 10:10

谢谢Banq的提示,实际我原来在分析Jive时和自己写这个查询时考虑到了Jive的sql生成独立于各数据库,改变数据库时不需要改变查询代码,通用兼容性非常优秀。
但我又考虑到另外一个问题:抛开具体数据库而采用通用中间件的情况下,就等于放弃了所有数据库的的优点,这实际上就是:通用和效率之间的取舍和平衡问题。
不过你的jdonFrame值得研究,我会找个时间好好读一下。