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