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

05-05-09 mybillliu
/*

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值得研究,我会找个时间好好读一下。

猜你喜欢