一个使用比较多的小系统在使用高峰的时候出现缓慢的情况，一些被认为是很简单的功能都很慢，如选择部门和人员，weblogic后台还有很多stuck的线程。最后发现是使用ibatis默认分页导致，它是使用的游标进行分页，下面来通过实验来比较两种方式的区别。
testPaging.jsp
<%@ page language="java" import="java.sql.Connection"%>
<%@ page language="java" import="java.sql.DriverManager"%>
<%@ page language="java" import="java.sql.ResultSet"%>
<%@ page language="java" import="java.sql.Statement"%>
<%@ page language="java" import="java.util.ArrayList"%>
<%@ page language="java" import="java.util.List"%>
<%@ page language="java" import="java.sql.Date"%>
<%@ page language="java" import="java.sql.Timestamp"%>
<html>
<body>
</body>
<script language="javascript">
<%!
public class TestVO {
private int object_id;
private String object_name;
private String OBJECT_TYPE;
private String OWNER;
private String STATUS;
private String TEMPORARY;
private String SECONDARY;
private String EDITION_NAME;
private int DATA_OBJECT_ID;
private Timestamp CREATED;
private Date LAST_DDL_TIME;
public Timestamp getCREATED() {
return CREATED;
}
public void setCREATED(Timestamp created) {
CREATED = created;
}
public int getDATA_OBJECT_ID() {
return DATA_OBJECT_ID;
}
public void setDATA_OBJECT_ID(int data_object_id) {
DATA_OBJECT_ID = data_object_id;
}
public String getEDITION_NAME() {
return EDITION_NAME;
}
public void setEDITION_NAME(String edition_name) {
EDITION_NAME = edition_name;
}
public Date getLAST_DDL_TIME() {
return LAST_DDL_TIME;
}
public void setLAST_DDL_TIME(Date last_ddl_time) {
LAST_DDL_TIME = last_ddl_time;
}
public int getObjectId() {
return object_id;
}
public void setObjectId(int object_id) {
this.object_id = object_id;
}
public String getObjectName() {
return object_name;
}
public void setObjectName(String object_name) {
this.object_name = object_name;
}
public String getOBJECT_TYPE() {
return OBJECT_TYPE;
}
public void setOBJECT_TYPE(String object_type) {
OBJECT_TYPE = object_type;
}
public String getOWNER() {
return OWNER;
}
public void setOWNER(String owner) {
OWNER = owner;
}
public String getSECONDARY() {
return SECONDARY;
}
public void setSECONDARY(String secondary) {
SECONDARY = secondary;
}
public String getSTATUS() {
return STATUS;
}
public void setSTATUS(String status) {
STATUS = status;
}
public String getTEMPORARY() {
return TEMPORARY;
}
public void setTEMPORARY(String temporary) {
TEMPORARY = temporary;
}
}
public class TestPaging {
public List<TestVO> physicalPaging(){
List list = new ArrayList();
TestVO testVO ;
Connection con = null;
Statement stmt = null;
ResultSet result = null;
StringBuffer strBuf = new StringBuffer();
strBuf.append("SELECT * ");
strBuf.append(" FROM (SELECT INNER_TABLE.*, ROWNUM OUTER_TABLE_ROWNUM ");
strBuf.append(" FROM (select * from test order by object_id) INNER_TABLE ");
strBuf.append(" WHERE ROWNUM <=120000) OUTER_TABLE ");
strBuf.append(" WHERE OUTER_TABLE_ROWNUM >119900 ");
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@10.10.11.11:1521:orcl";
String user = "DEV";
String password = "DEV";
con = DriverManager.getConnection(url, user, password);
stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
result = stmt.executeQuery(strBuf.toString());
while (result.next ()) {
testVO = new TestVO();
testVO.setObjectId(result.getInt("object_id"));
testVO.setObjectName(result.getString("object_name"));
list.add(testVO);
}
return list;
}catch (Exception e){
e.printStackTrace();
return list;
}
finally{
try{
if (result != null)
result.close();
if (stmt != null)
stmt.close();
if (con != null)
con.close();
}
catch (Exception e){
e.printStackTrace();
}
}
}
public List<TestVO> memoryPaging(){
List list = new ArrayList();
TestVO testVO ;
Connection con = null;
Statement stmt = null;
ResultSet result = null;
String sql="select * from test";
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@10.10.11.11:1521:orcl";
String user = "DEV";
String password = "DEV";
con = DriverManager.getConnection(url, user, password);
stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
result = stmt.executeQuery(sql);
int j=0;
while(result.next() && j++<119900){
}
int i=0;
while(result.next() && i++<100){
testVO = new TestVO();
testVO.setObjectId(result.getInt("object_id"));
testVO.setObjectName(result.getString("object_name"));
list.add(testVO);
}
return list;
}catch (Exception e){
e.printStackTrace();
return list;
}
finally{
try{
if (result != null)
result.close();
if (stmt != null)
stmt.close();
if (con != null)
con.close();
}
catch (Exception e){
e.printStackTrace();
}
}
}
}
%>
<%
TestPaging testPaging = new TestPaging();
long startTime = System.currentTimeMillis();
testPaging.memoryPaging();
long endTime = System.currentTimeMillis();
System.out.println("游标分页耗时："+(endTime-startTime)+"ms");
long startTime1 = System.currentTimeMillis();
testPaging.physicalPaging();
long endTime1 = System.currentTimeMillis();
System.out.println("SQL分页耗时："+(endTime1-startTime1)+"ms");
%>
</script>
</html>
执行时间的对比：
游标分页耗时：18675msSQL分页耗时：550ms
消耗的内存对比：
游标分页：20M
SQL分页：1M