PreparedStatement，CallableStatement和性能注意事项

问题描述：

我有一个需要从我的Java程序调用的 oracle存储过程。我曾使用 CallableStatement 将参数传递给存储过程。我正在使用oracle瘦驱动程序（在Web逻辑服务器中配置相对于相关的jndi条目）。此存储过程没有任何OUT值。此存储过程接受一个数值，并根据收到的值在数据库中执行大量更新。

I have a oracle stored proc that needs to be called from my Java program. I had used CallableStatement to pass parameters to the stored proc. I am using oracle thin driver (configured in web logic server against the relevant jndi entry).This stored proc does not have any OUT values. This stored proc accepts a numeric value and does a lot of updates in the db based on the value received.

我得到一个连接对象然后在循环中调用这个存储过程（20次传递20个数字）。当我从oracle客户端直接调用此存储过程时，执行将在2-3秒内完成。但是我的java代码无法预测这种行为。有些呼叫甚至需要30-40秒才能完成。

I get a connection object and then call this stored proc in loop (20 times for passing 20 numbers). When I directly call this stored proc from an oracle client , the execution gets completed in 2-3 seconds. However the behaviour is not predictable from my java code. Some of the calls take even 30-40 seconds to get completed.

我尝试使用 PreparedStatement 而不是 CallableStatement 和可以看到边际绩效改善（虽然行为仍然不一致）。

I tried to use PreparedStatement instead of CallableStatement and could see marginal performance improvement (though the behaviour is still inconsistent).

在我的情况下使用是否可以鉴于storedproc没有任何OUT参数，PreparedStatement 而不是 CallableStatement ？

有什么理由吗？为什么 PreparedStatement 比 CallableStatement 有一些性能提升，还是我可能观察不到的东西？

有没有更好的方法来解决这个性能问题？

Is it OK in my case to use PreparedStatement instead of CallableStatement given that the storedproc does not have any OUT parameters?
Is there any reason why PreparedStatement has some performance gain over CallableStatement or is it something that I might have observed incorrectly?
Is there a better approach for solving this performance issue?

答

从你的评论中，你在循环中有prepareCall。预处理语句（和可调用语句）的一个优点是，您可以准备一次，然后交换参数中传递的值;每次准备调用时都会有开销，所以如果你可以把它带到循环之外，你可能会发现运行时间减少了。您可能会发现关闭AutoCommit也会有所帮助，因为每次提交都会产生开销。

From your comment, you have prepareCall inside your loop. An advantage of prepared statements (and callable statements) is that you can prepare it once, and then swap out the values passed in the parameters; there is overhead each time the call is prepared, so if you could bring that outside of your loop, you may find that run time decreases. You may find that turning off AutoCommit also helps, as there is overhead with each commit.

conn.setAutoCommit(false);
CallableStatement stmt = conn.prepareCall(sql);
while(true) {
    stmt.setInt(1, value);
    stmt.execute();
}
conn.commit();
conn.setAutoCommit(true);

（ conn.setAutoCommit（true）是吗提交，但我发现更清楚明确）。

(conn.setAutoCommit(true) does commit, but I find it clearer to be explicit).

PreparedStatement，CallableStatement和性能注意事项

相关推荐