Access数据库的高效分页

最近在做一个blogs,因为空间的限制,只能用Access数据库

测试了一下,当数据量2000+的时候分页比较慢

我的SQL语句是

sql = "select top 10 * from tb_article where id not in (select top " + current * 10 + " id from tb_article order by id desc ) order by id desc";

current 是页码

随后想到一种:

sql = "select * from tb_article where id between "+10*current +" and "+10*(current+1) +" order by id desc";

不过在删除存在的情况下会有问题

第三种想到的方法:       

sql = "select top 10 * from tb_article where id >"+current*10 +" order by id desc";

同样,在删除情况下还是会有问题

最后又想到一个思路,还是用between

同时新建一个数据表,里面有三个字段,(页码,首记录ID,尾记录ID)

每次分页的时候首先查询这个表

应该速度会加快.

目前仅在思想中,未测试

不过此办法比较麻烦,最后使用如下方法

int total = GetListCount(current);

   sql = "select * from tb_article where id between

("+total+"-(SELECT MAX(id)   FROM (SELECT TOP " + (current + 1) * 10 + " id FROM tb_article order by id))+1)

and

("+total+"-(SELECT MAX(id)   FROM (SELECT TOP " + current * 10 + " id FROM tb_article order by id)))

order by id desc ";