使用 JDBC for Oracle 迭代一个 ResultSet 需要很多时间大约 16s？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17744090/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Iterating a ResultSet using the JDBC for Oracle takes a lot of time about 16s?
提问by hershey92
while( result_set.next() )
{
...
}
I have use System.nanoTime()and calculated the time, for each iteration the time taken is in milliseconds but the overall loop takes about 16s. I am considering a possible reason that the condition test is taking a lot of time, the next()function.
我已经使用System.nanoTime()并计算了时间，每次迭代所用的时间以毫秒为单位，但整个循环大约需要 16 秒。我正在考虑条件测试花费大量时间的一个可能原因，即next()函数。
FYI I am connecting to a remote database server and the select query that I make is completed in milliseconds again calculated using the above mentioned method. Any reasons about why it's happening and how I can bring the time to iterate the resultset down to at max a second?
仅供参考，我正在连接到远程数据库服务器，并且使用上述方法再次计算出我所做的选择查询以毫秒为单位完成。关于为什么会发生这种情况的任何原因以及我如何将时间将结果集迭代到最多一秒？
EDIT:
编辑：
I am dealing with about 4000 records and each record contians about 10 columns each having a size of about 10 chars
我正在处理大约 4000 条记录，每条记录包含大约 10 列，每列的大小约为 10 个字符
EDIT2Thanks setFetchsize() did the magic, awesome, awesome
EDIT2感谢 setFetchsize() 发挥了神奇的作用，真棒，真棒
回答by Andreas Fester
I have set up a table with 4000 rows and 10 columns with 10 characters each and made a simple performance test using the following approach (RealTimeCounteris a class which measures the real time between start()and stop()):
我建立了一个包含 4000 行和 10 列的表，每列有 10 个字符，并使用以下方法进行了简单的性能测试（RealTimeCounter是一个测量start()和之间实时时间的类stop()）：
List<String> myResult = new ArrayList<>();
ResultSet rs = s.executeQuery("SELECT * FROM Performance");
RealTimeCounter rtc = new RealTimeCounter();
rtc.start();
while(rs.next()) {
myResult.add(rs.getString(1));
}
rtc.stop();
System.out.println(rtc);
Results:
结果：
- Default fetch size: execution time is approx. 20 sec
- fetch size = 100: execution time is approx 2.2 sec
- fetch size = 500: execution time is approx 450 msec
- fetch size = 2000: execution time is approx 120 msec
- fetch size = 4000: execution time is approx 50 msec
- fetch size = 4001: execution time is approx 10 msec(!!)
- 默认提取大小：执行时间约为。20 秒
- fetch size = 100：执行时间约为2.2 秒
- fetch size = 500：执行时间约为450 毫秒
- 获取大小 = 2000：执行时间约为120 毫秒
- 获取大小 = 4000：执行时间约为50 毫秒
- 获取大小 = 4001：执行时间约为10 毫秒（！！）
So, the fetch size doeshave a significant impact on the execution speed.
因此，获取大小确实对执行速度有显着影响。
Note that, on the other hand, the fetch size has some impact on memory consumption. Interestingly enough, a quick analysis using Runtime.getRuntime().freeMemory();before and after the above code showed that the impact is much less than I would expect, though. The numbers I got are:
请注意，另一方面，提取大小对内存消耗有一些影响。有趣的是，使用Runtime.getRuntime().freeMemory();上述代码前后的快速分析表明，影响比我预期的要小得多。我得到的数字是：
- Default fetch size: 665k
- fetch size = 100: 665k
- fetch size = 500: 665k
- fetch size = 2000: 743k
- fetch size = 4000: 821k
- fetch size = 4001: 861k
- 默认获取大小：665k
- 获取大小 = 100: 665k
- 获取大小 = 500: 665k
- 获取大小 = 2000: 743k
- 获取大小 = 4000: 821k
- 获取大小 = 4001: 861k
回答by Evgeniy Dorofeev
Try to change ResultSet fetch size. By default, Oracle JDBC driver receives the result set only 10 rows at a time from the database cursor. It may not significantly improve performance but there arent many other options to make driver work faster.
尝试更改 ResultSet 提取大小。默认情况下，Oracle JDBC 驱动程序一次仅从数据库游标接收 10 行结果集。它可能不会显着提高性能，但没有许多其他选项可以使驱动程序工作得更快。
