Java：使用ResultSet.next()执行含有rownum的SQL语句速度缓慢

ゞ浴缸里的玫瑰 2022-10-02 12:57 165阅读 0赞

在使用Oracle数据库进行分页查询时，经常会用到如下SQL：

select tm.* from (select rownum rm, t.* xmlrecord from testtable t) tm where tm.rm > ? and tm.rm <= ?

使用的java代码如下：

int startIdx = 0;
    int endIdx = 10000;
    
    String sql = "select tm.* from (select rownum rm, t.* xmlrecord from testtable t) tm where tm.rm > ? and tm.rm <= ?";
    
    try (Connection conn = dataSource.getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
    
    	ps.setInt(1, startIdx);
    	ps.setInt(2, endIdx);
    
    	try (ResultSet rs = ps.executeQuery();) {
    
    		while (rs.next()) {
    			String id = rs.getString(2);
    
    			System.out.println("id="+id);
    		}
    	}
    }

当使用以上代码时，会发现当取完最后一条记录后，再执行rs.next()时，本来希望返回false后跳出循环，但rs.next()会执行非常长的时间。解决的方法是不让rs.next()来判断下一条记录不存在，而在代码通过计数来实现：

int startIdx = 0;
    int endIdx = 10000;
    
    int i = 0;
    int count = endIdx - startIdx;
    
    String sql = "select tm.* from (select rownum rm, t.* xmlrecord from testtable t) tm where tm.rm > ? and tm.rm <= ?";
    
    try (Connection conn = dataSource.getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
    
    	ps.setInt(1, startIdx);
    	ps.setInt(2, endIdx);
    
    	try (ResultSet rs = ps.executeQuery();) {
    
    		while (rs.next()) {
    			i++;
    
    			String id = rs.getString(2);
    
    			System.out.println("id="+id);
    			
    			if(i == count) {
    			  break;
    			}
    		}
    	}
    }

如果代码中设置了fetchSize，并且fetchSize不能被count整除时，在取最后一片数据时，rs.next()也会执行很长时间：

int startIdx = 0;
    int endIdx = 10000;
    
    String sql = "select tm.* from (select rownum rm, t.* xmlrecord from testtable t) tm where tm.rm > ? and tm.rm <= ?";
    
    try (Connection conn = dataSource.getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
    	ps.setFetchSize(300);
    	ps.setInt(1, startIdx);
    	ps.setInt(2, endIdx);
    
    	try (ResultSet rs = ps.executeQuery();) {
    
    		while (rs.next()) {
    			String id = rs.getString(2);
    
    			System.out.println("id="+id);
    		}
    	}
    }

以上代码中，当取得9900条数据后，再取下一个300条时，rs.next()就会执行很长时间，可能是由于取不到一个完整的300条记录造成的。解决方法是将fetchSize设置成能被count整除的数字，比如：

ps.setFetchSize(500);

在以上两种状况下，为什么rs.next()会执行很长时间，还不是很清楚，但可以通过上述方式解决。至于为什么会有这个问题，有知道原因的朋友，请不吝赐教。