准备的语句和性能 [英] PreparedStatements and performance
问题描述
所以我不断听到PreparedStatements对于性能有好处。
So I keep hearing that PreparedStatements are good for performance.
我们有一个Java应用程序，我们使用常规的'Statement'比我们使用'PreparedStatement '。在尝试使用更多的PreparedStatements时，我试图更全面地了解PreparedStatements如何在客户端和服务器端工作。
We have a Java application in which we use the regular 'Statement' more than we use the 'PreparedStatement'. While trying to move towards using more PreparedStatements, I am trying to get a more thorough understanding of how PreparedStatements work - on the client side and the server side.
因此，如果我们有一些典型的CRUD操作和在应用程序中反复更新对象，它有帮助使用PS吗？我理解，我们将不得不关闭PS每次否则它将导致一个游标泄漏。
So if we have some typical CRUD operations and update an object repeatedly in the application, does it help to use a PS? I understand that we will have to close the PS every time otherwise it will result in a cursor leak.
那么它是如何帮助性能？驱动程序是否缓存预编译的语句，并给我一个副本下一次我做connection.prepareStatement？或者DB服务器帮助？
So how does it help with performance? Does the driver cache the precompiled statement and give me a copy the next time I do connection.prepareStatement? Or does the DB server help?
我理解关于PreparedStatements的安全优点的论点，我感谢下面强调它的答案。但我真的想把这个讨论集中在PreparedStatements的性能好处。
I understand the argument about the security benefits of PreparedStatements and I appreciate the answers below which emphasize it. However I really want to keep this discussion focused on the performance benefits of PreparedStatements.
更新：当我说更新数据，我真的意味着更多的方法的方法随机叫几次。我理解下面的答案中的优点，它要求在循环内重用语句。
Update: When I say update data, I really mean more in terms of that method randomly being called several times. I understand the advantage in the answer offered below which asks to re-use the statement inside a loop.
// some code blah blah
update();
// some more code blah blah
update();
....
public void update () throws SQLException{
try{
PreparedStatement ps = connection.prepareStatement("some sql");
ps.setString(1, "foobar1");
ps.setString(2, "foobar2");
ps.execute();
}finally {
ps.close();
}
}
方式实际上重用'ps'java对象，我理解，实际的connection.prepareStatement调用是相当昂贵的。
There is no way to actually reuse the 'ps' java object and I understand that the actual connection.prepareStatement call is quite expensive.
这是什么让我回到原来的问题。这是一些sqlPreparedStatement仍然被缓存和重用的覆盖，我不知道吗？
Which is what brings me back to the original question. Is this "some sql" PreparedStatement still being cached and reused under the covers that I dont know about?
我还应该提到我们支持几个数据库。
I should also mention that we support several databases.
提前感谢。
推荐答案
准备语句主要是关于性能的概念
The notion that prepared statements are primarily about performance is something of a misconception, although it's quite a common one.
另一个海报提到他在Oracle和SQL Server中提高了大约20％的速度。我注意到一个类似的数字与MySQL。事实证明，解析查询并不是所涉及的工作中的重要部分。在一个非常繁忙的数据库系统上，也不清楚查询解析会影响整体吞吐量：总的来说，它可能只是占用CPU时间，否则当数据从磁盘回来时，CPU时间将是空闲的。
Another poster mentioned that he noted a speed improvement of about 20% in Oracle and SQL Server. I've noted a similar figure with MySQL. It turns out that parsing the query just isn't such a significant part of the work involved. On a very busy database system, it's also not clear that query parsing will affect overall throughput: overall, it'll probably just be using up CPU time that would otherwise be idle while data was coming back from the disk.
因此，作为使用预准备语句的原因，防止SQL注入攻击远远超过性能提升。如果你不担心SQL注入攻击，你可能应该... ...
So as a reason for using prepared statements, the protection against SQL injection attacks far outweighs the performance improvement. And if you're not worried about SQL injection attacks, you probably should be...
这篇关于准备的语句和性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
