网站 › 『 Sybase ASE 』 › [转帖]在SYBASE中进行分页查询的方法

ehxz 发表于 2007-11-5 12:35:05

[转帖]在SYBASE中进行分页查询的方法

<p>在数据库表中如果存在大量的数据，那么这些数据如果一次都查询出来，会造成很大的延迟，甚至引起内存溢出。所以在查询大规模的数据时，往往需要做分页查询，一次只查询出小部分的数据。<br/>在以前我们通常的做法是采用游标，移动到指定行号的记录行后，再按要提取的记录数来取数据。从表面上看，是解决了记录的问题，但是从实际应用上，在建立游标的过程中，需要消耗大量的系统资源，如果数据量大，查询的效率会大大降低。<br/>当前最为常用的方法是对数据库表建唯一索引，利用临时表存储在指定记录行数前的数据，再通过索引递增的原理，返回大于前面记录的要提取的记录数的数据。在SYBASE 12.5.3版本之前，我们采用的分页查询代码所示如下：<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;Object[] argus =<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; {<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;Integer.toString(recNum),<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;Integer.toString(recNum * (pageNum - 1)),<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;table,<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;cond1,<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;cond2,<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;field };<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;StringBuffer sqlstr = new StringBuffer("set rowcount {1}\n");<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;sqlstr.append(<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; "select id into #tmptable from {2} {4} order by id desc\n");<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;sqlstr.append("set rowcount {0}\n");<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;sqlstr.append(<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; "select {5} from {2} where id&lt;(select min(id) from #tmptable) {3} order by id desc\n");<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;sqlstr.append("drop table #tmptable");<br/>从SYBASE 12.5.3版本之后，在查询的SQL中提供了TOP语法支持，利用它可以简化查询的代码，并且避免了忘记将rowcount复位，造成的后续查询出错。代码如下：<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;Object[] argus =<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; {<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;Integer.toString(recNum),<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;Integer.toString(recNum * (pageNum - 1)),<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;table,<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;cond1,<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;cond2,<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;field };<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;StringBuffer sqlstr = new StringBuffer("");<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;sqlstr.append(<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; "select top {1} id into #tmptable from {2} {4} order by id desc\n");<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;sqlstr.append(<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; "select top {0} {5} from {2} where id&lt;(select min(id) from #tmptable) {3} order by id desc\n");<br/>&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;sqlstr.append("drop table #tmptable");<br/>同样可以在存储过程中采用以上的原理，加快分页查询的速度。当然如果没有按序排列的ID，这种方法就不可用了，这往往是最初数据库设计时考虑不周造成的。<br/>在实际项目开发中，我们发现上述的方法对大规模的数据在查询到最后几页时，效率要下降一些，如果这些数据更新不频繁，为了提高多用户并发查询的速度，可以将对相同表、相同查询条件的临时表改成数据不增删就不变的表，表中一次将所有符合条件的ID存放起来，增加一个Identity字段，然后通过Identity字段做到分页查询。这种方法对没有按序排列的ID的数据库表同样适用。<br/></p><div class="msgheader">QUOTE:</div><div class="msgborder"><p>FROM:http://bbs.chinaunix.net/thread-1011442-1-1.html</p><p>作者：<a class="bold" href="http://bbs.chinaunix.net/profile-uid-181706.html" target="_blank">jarjar</a>
                </p></div>

查看完整版本: [转帖]在SYBASE中进行分页查询的方法