JDBC 结果集关闭
我正在对我的 Java 应用程序进行分析，并发现了 jdbcPreparedStatement 调用的一些有趣的统计数据：
下面给出的是环境详细信息： 数据库：Sybase SQL Anywhere 10.0.1 驱动程序：com.sybase.jdbc3.jdbc.SybDriver 连接池：c3p0 JRE：1.6.0_05
下面给出了有问题的代码：
try {
ps = conn.prepareStatement(sql);
ps.setDouble(...);
rs = ps.executeQuery();
......
return xyz;
}
finally {
try {
if (rs != null) rs.close();
if (ps != null) ps.close();
}
catch (SQLException sqlEx) {
}
}
从 JProfiler 统计数据中，我发现这个特定的 resultspace.close() 语句单独花费了大量时间。它的时间从 25 毫秒到 320 秒不等，而对于本质上相同的其他代码块，我发现这需要接近 20 微秒。
为了确定起见，我多次运行此性能测试并确认了此数据。我对这种行为感到困惑——想法？
I am doing profiling of my Java application and found some interesting statistics for a jdbc PreparedStatement call:
Given below is the environment details:
Database: Sybase SQL Anywhere 10.0.1
Driver: com.sybase.jdbc3.jdbc.SybDriver
connection pool: c3p0
JRE: 1.6.0_05
The code in question is given below:
try {
ps = conn.prepareStatement(sql);
ps.setDouble(...);
rs = ps.executeQuery();
......
return xyz;
}
finally {
try {
if (rs != null) rs.close();
if (ps != null) ps.close();
}
catch (SQLException sqlEx) {
}
}
From JProfiler stats, I find that this particular resultspace.close() statement alone takes a large amount of time. It varies from 25 ms to 320s while for other code blocks which are of identical in nature, i find that this takes close to 20 microseconds.
Just to be sure, I ran this performance test multiple times and confirmed this data. I am puzzled by this behaviour - Thoughts?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
此性能是特定于 JDBC 驱动程序的。 C3P0连接池不应该对其产生任何影响。我建议使用更新的或其他 JDBC 驱动程序重新测试它。 Sybase 驱动程序的替代方案是 jTDS 驱动程序。我不确定与 Sybase 驱动程序相比它的性能如何，但与 Microsoft 自己的 MSSQL JDBC 驱动程序相比，它的性能非常。
与实际问题无关，您实际上应该在自己的
try块中调用每个close()方法，否则无法保证它们全部 em> 关闭。如果第一次关闭抛出SQLException，则后续的关闭调用将不会执行。 Apache Commons DbUtils 可能有助于采取样板代码消失了。This performance is JDBC driver specific. The C3P0 connection pool should not have any influence on it. I would suggest to retest it with a newer or another JDBC driver. An alternative to the Sybase driver is the jTDS driver. I am not sure how this performs compared to the Sybase driver, but it is known to be very performant as compared to Microsoft's own MSSQL JDBC driver.
Unrelated to the actual problem, you should in fact call the
close()methods each in its owntryblock, else there's no guarantee that they will all be closed. If the first close throwsSQLException, the subsequent close calls won't be executed. The Apache Commons DbUtils may help to take the boilerplate code away.关于半相关的说明，请查看 Apache Commons DbUtils 和 Dbutils.closeQuietly() 方法，用于通过正确的异常处理轻松管理按正确顺序关闭连接/语句/结果集。
On a semi-related note, check out Apache Commons DbUtils and the Dbutils.closeQuietly() method for easily managing closing of connections/statements/resultsets in the correct order with correct exception handling.
方法调用实际上在延迟期间导致了 CPU 负载还是只是在等待？关闭结果集很可能涉及与数据库的远程通信，我的猜测是在某些情况下这可能需要一段时间。
Is the method call actually causing CPU load during the delay or is it simply waiting? Closing the ResultSet most likely involves remote communication with the database and my guess is that there are some circumstances where this may take a while.
如果该语句是一个选择并且您没有消耗所有数据，请尝试在关闭该语句之前取消该语句。
If the statement is a select and you are not consuming all the data try canceling the statement before closing it.