ibatis默认实现分页与Oracle SQL分页性能比较

时间:2021-09-17 18:32:51

        一个使用比较多的小系统在使用高峰的时候出现缓慢的情况,一些被认为是很简单的功能都很慢,如选择部门和人员,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>  

执行时间的对比:

游标分页耗时:18675ms
SQL分页耗时:550ms


消耗的内存对比:

游标分页:20M

SQL分页:1M