Access分頁(yè)方案
開(kāi)門(mén)見(jiàn)上，本文主要是回答一下三個(gè)問(wèn)題：
- Access是否存在更有效率的分頁(yè)方法？
- 現(xiàn)有Access大數(shù)據(jù)量10萬(wàn)條數(shù)據(jù)分頁(yè)的效率測(cè)試
- Access的數(shù)據(jù)承載量到底有多大？
相信很多ASP的站點(diǎn)還在使用Access數(shù)據(jù)庫(kù)，因?yàn)锳ccess數(shù)據(jù)庫(kù)無(wú)須開(kāi)專(zhuān)門(mén)的數(shù)據(jù)庫(kù)空間，調(diào)用，遷移也方便，節(jié)省費(fèi)用。另外對(duì)網(wǎng)站搭建者的專(zhuān)業(yè)能力要求也相對(duì)低一些。但隨著網(wǎng)站的運(yùn)行，數(shù)據(jù)庫(kù)體積越來(lái)越大，數(shù)據(jù)量也從最初的幾百條到了現(xiàn)在的上萬(wàn)條，上十萬(wàn)條甚至更多。于是因數(shù)據(jù)應(yīng)用級(jí)別的改變帶來(lái)的各種各樣的應(yīng)用問(wèn)題出現(xiàn)了。而其中大數(shù)據(jù)量的列表分頁(yè)效率問(wèn)題更是讓很多人頭疼。筆者隨便通過(guò)“大數(shù)據(jù)量分頁(yè)效率”，“Access 分頁(yè)”等關(guān)鍵詞分別百度和谷歌了一下，發(fā)現(xiàn)有此疑問(wèn)的大有人在。很多網(wǎng)頁(yè)上也給出了不同的解決辦法。那么，這些方法到底能達(dá)到優(yōu)化效率，提高速度的目的嗎？
先讓我們來(lái)看看以下的幾個(gè)Access分頁(yè)優(yōu)化方案，當(dāng)然如果你直接將數(shù)據(jù)庫(kù)升級(jí)到SQL Server，那么有更好的諸如存儲(chǔ)過(guò)程等方法。今天我們就討論一下Access大數(shù)據(jù)量?jī)?yōu)化分頁(yè)方法，以及Access到底能承受多少數(shù)據(jù)量。
方案一：利用ado本身的結(jié)果集的pagesize，AbsolutePage的屬性來(lái)進(jìn)行分頁(yè)
程序示例：（僅供示意，完善的各種條件判斷自行添加）
- MaxPerPage=20
- page=cint(request("page"))
- sql="select * from 表 where 條件 order by 排序條件"
- set rst=server.CreateObject("adodb.recordset")
- rst.open sql,conn,1,1
- rst.pagesize=MaxPerPage
- rst.AbsolutePage = Page '將記錄定位到對(duì)應(yīng)頁(yè)數(shù)的第一條
- for i=1 to MaxPerPage 循環(huán)列表
- rst.movenext
- if rst.eof then exit for
- next
這個(gè)方法是最為常用的Access分頁(yè)方法。
缺點(diǎn)：每次都要讀入符合條件的所有記錄，然后再定位于對(duì)應(yīng)頁(yè)的記錄。當(dāng)數(shù)據(jù)量大的時(shí)候，效率就十分的低下。
與此相似的方法是利用ado的move方法，每次將記錄集游標(biāo)移動(dòng) (page-1)*pagesize ，就實(shí)現(xiàn)了了記錄的分頁(yè)。經(jīng)過(guò)測(cè)試，效率與方案一大致相同。
方案二：
1.設(shè)置一個(gè)自增長(zhǎng)字段.并且該字段為INDEX.
2.由于是 Access ,所以,只能是前臺(tái)分頁(yè).自增長(zhǎng)字段目的,就是為了實(shí)現(xiàn)分頁(yè)功能.
1> 記錄用戶(hù)前頁(yè)的最后一個(gè) 自增值 ,例如 M .
2> 下一頁(yè),取下一頁(yè)的開(kāi)始值.M+1 ,結(jié)束值: M+1+1.5*PAGESIZE (注:由于數(shù)據(jù)庫(kù)會(huì)有增刪操作,故應(yīng)該取頁(yè)大小應(yīng)該有一個(gè)系數(shù),你可以根據(jù)情況自定一個(gè)1大的系數(shù).
3> 前臺(tái)循環(huán)取 RS 的前 PAGESIZE 條, 寫(xiě)到一個(gè) 新的RS中,并返回.
這個(gè)方案通過(guò)自增值來(lái)分部截取不同分頁(yè)的數(shù)據(jù)列表，文中考慮到數(shù)據(jù)庫(kù)有增刪操作，所以加入了一個(gè)系數(shù)的概念，這是一個(gè)不得已的做法。這個(gè)方案可以保證分頁(yè)效率，但只能運(yùn)用于增刪不太頻繁（自增值字段相鄰記錄的值相差不多的情況）的數(shù)據(jù)表。
方案三：not in 方法。
這個(gè)方案在很多網(wǎng)站上都轉(zhuǎn)載。據(jù)說(shuō)對(duì)于越往前的分頁(yè)效率提高越明顯。我一直有所懷疑，因?yàn)椤皀ot in”本身就是個(gè)耗費(fèi)資源的算法。很難相信一個(gè)低效率的方法能提高大數(shù)據(jù)量分頁(yè)的效率。示例如下：
- sql="select top 12 * from 表 where Id not in(select top page*pagesize Id from 表 order by id desc) order by Id desc"
如果是第9頁(yè)，每頁(yè)20條即
- select top 20 * from 表 where Id not in(select top 9*20 Id from 表 order by id desc) order by Id desc
原理即：選擇top 20 的記錄，條件是id不在前面分頁(yè)的記錄ID里。通過(guò)這種方式過(guò)濾掉前面分頁(yè)的記錄，然后通過(guò)top高效率的方式獲取當(dāng)頁(yè)的記錄。
“top”確實(shí)高效，但是“not in”呢？
于是我直接用這種方法測(cè)試了一下，測(cè)試條件：10萬(wàn)條數(shù)據(jù)。點(diǎn)擊查詢(xún).......... MY GOD，長(zhǎng)時(shí)間無(wú)響應(yīng)，最后Ctrl+Alt+Delete 結(jié)束任務(wù)。再試，結(jié)果同樣如此。于是改變一下測(cè)試條件，變成1000條數(shù)據(jù)，OK，結(jié)果顯示非常順利。
結(jié)論：如果你是大數(shù)據(jù)量分頁(yè)，還是不要用這種方法，會(huì)死人的。
方案四：
- "select * from (select top "&pagesize&" * from (select top "&page*pagesize&" * from 表" order by id desc) order by id) order by id desc"
這個(gè)方法簡(jiǎn)單說(shuō)來(lái)，就是選取當(dāng)前頁(yè)及小于當(dāng)前分頁(yè)的所有記錄，再通過(guò)“Top”方式選取當(dāng)前頁(yè)的記錄。
這個(gè)方法沒(méi)有出現(xiàn)效率低的語(yǔ)句，雖然至少要select兩次（示例select了三次是為了排序）。但是效率應(yīng)該不錯(cuò)。且越靠前的分頁(yè)應(yīng)該越明顯。
如果還想節(jié)省效率，可以只select兩次。
假如記錄ID為1-100，每頁(yè)5條。現(xiàn)在顯示第4頁(yè)，排序?yàn)榈剐颉?/P>
執(zhí)行順序：
1) 選擇前4頁(yè)的數(shù)據(jù)，即100-81共20條數(shù)據(jù)
2) 從這20條數(shù)據(jù)中選擇最小的5條，即81-85。
3) 將選擇的5條按倒序排，即成為 85-81。
如果節(jié)省第三步也可，只不過(guò)顯示變成
第一頁(yè)：96，97，98，99，100
第二頁(yè)：91，92，93，94，95
其實(shí)也不錯(cuò)。
光說(shuō)沒(méi)用，最終看測(cè)試結(jié)果。我在相同的數(shù)據(jù)條件，服務(wù)器配置下，分別對(duì)方案一中的兩種方法和方案四進(jìn)行了Access分頁(yè)效率測(cè)試，測(cè)試數(shù)據(jù)如下
測(cè)試條件：
>10萬(wàn)條；pagesize=20；
分頁(yè)總數(shù)>5000頁(yè)；
順便也進(jìn)行了一下“select 部分字段”和“select 所有字段”的對(duì)比測(cè)試。
從上面的測(cè)試結(jié)果來(lái)看，方案三的優(yōu)勢(shì)還是比較明顯的。而到5000頁(yè)的效率基本上和前兩種方法差不多，甚至仍然有一定得優(yōu)勢(shì)。
另外，很多人在寫(xiě)select語(yǔ)句時(shí)， 習(xí)慣 select * from 表，這不是一個(gè)好習(xí)慣。上面的Access分頁(yè)測(cè)試結(jié)果表明，還是按需索要，按需供應(yīng)的好，需要什么字段，就select什么字段。能夠極大的節(jié)省服務(wù)器資源。
很多網(wǎng)友提到Access時(shí)都不免的輕視，“你還在用Access？”，“還不換SQL Server？”，“用Access你還想多快？”。其實(shí)在我的經(jīng)驗(yàn)看來(lái)，即便是在10萬(wàn)條的應(yīng)用級(jí)別上。Access常常比SQL Server快。因?yàn)镾QL Server需要額外連接，且多了一個(gè)帶寬連接因素的影響（當(dāng)然，網(wǎng)站服務(wù)器和數(shù)據(jù)庫(kù)服務(wù)器運(yùn)行速度和帶寬都很OK，那沒(méi)話(huà)說(shuō)）。
SQL Server 在更高數(shù)據(jù)級(jí)別上的速度優(yōu)勢(shì)還是比較明顯，畢竟與Access不是一個(gè)級(jí)別的產(chǎn)品。
為了探索一下Access數(shù)據(jù)庫(kù)的極限。在40萬(wàn)條數(shù)據(jù)的情況下進(jìn)行了上述分頁(yè)測(cè)試。速度確實(shí)大打折扣。但是第三種方案在一萬(wàn)頁(yè)內(nèi)還是表現(xiàn)不錯(cuò)的。此時(shí)數(shù)據(jù)庫(kù)已經(jīng)達(dá)到400多兆。再結(jié)合之前處理過(guò)的幾個(gè)4，500兆的Access數(shù)據(jù)庫(kù)。我認(rèn)為40萬(wàn)條數(shù)據(jù)是Access數(shù)據(jù)庫(kù)在一般應(yīng)用的一個(gè)界限，但不是極限。超過(guò)這個(gè)數(shù)，就需要在程序優(yōu)化上做太多的工作。就有些不太值了。
原文鏈接：http://www.cnblogs.com/huanghai/archive/2011/03/19/1988627.html
【編輯推薦】
