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

叁歲伎倆 2022-06-08 14:40 171阅读 0赞

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