Java PreparedStatement 和性能

PreparedStatements and performance

提问by Kapsh

So I keep hearing that PreparedStatements are good for performance.

所以我一直听说 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.

我们有一个 Java 应用程序，在该应用程序中，我们使用常规“Statement”的次数比使用“PreparedStatement”的次数要多。在尝试使用更多 PreparedStatements 的同时，我试图更透彻地了解 PreparedStatements 的工作原理 - 在客户端和服务器端。

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.

那么如果我们有一些典型的 CRUD 操作并在应用程序中重复更新一个对象，那么使用 PS 有帮助吗？我知道我们每次都必须关闭 PS，否则会导致游标泄漏。

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?

那么它对性能有什么帮助呢？驱动程序是否缓存预编译语句并在我下次执行 connection.prepareStatement 时给我一份副本？或者数据库服务器有帮助吗？

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.

我理解关于 PreparedStatements 的安全优势的论点，我很欣赏下面强调它的答案。然而，我真的想让这个讨论集中在 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();

 }

}

There is no way to actually reuse the 'ps' java object and I understand that the actual connection.prepareStatement call is quite expensive.

没有办法真正重用“ps”java 对象，我知道实际的 connection.prepareStatement 调用非常昂贵。

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?

这就是让我回到最初的问题的原因。这个“一些 sql” PreparedStatement 是否仍在我不知道的幕后缓存和重用？

I should also mention that we support several databases.

我还应该提到我们支持多个数据库。

Thanks in advance.

提前致谢。

回答by Jon

Prepared statements are indeed cached after their first use, which is what they provide in performance over standard statements. If your statement doesn't change then it's advised to use this method. They are generally stored within a statement cache for alter use.

准备好的语句在第一次使用后确实会被缓存，这就是它们在性能上优于标准语句的情况。如果你的语句没有改变，那么建议使用这种方法。它们通常存储在语句缓存中以供更改使用。

More info can be found here:

更多信息可以在这里找到：

http://www.theserverside.com/tt/articles/article.tss?l=Prepared-Statments

http://www.theserverside.com/tt/articles/article.tss?l=Prepared-Statments

and you might want to look at Spring JDBCTemplate as an alternative to using JDBC directly.

并且您可能希望将 Spring JDBCTemplate 视为直接使用 JDBC 的替代方法。

http://static.springframework.org/spring/docs/2.0.x/reference/jdbc.html

http://static.springframework.org/spring/docs/2.0.x/reference/jdbc.html

回答by u7867

Prepared statements can improve performance when re-using the same statement that you prepared:

重用准备好的语句时，准备好的语句可以提高性能：

PreparedStatement ps = connection.prepare("SOME SQL");

for (Data data : dataList) {
  ps.setInt(1, data.getId());
  ps.setString(2, data.getValue();
  ps.executeUpdate();
}

ps.close();

This is much faster than creating the statement in the loop.

这比在循环中创建语句要快得多。

Some platforms also cache prepared statements so that even if you close them they can be reconstructed more quickly.

一些平台还缓存准备好的语句，以便即使您关闭它们也可以更快地重建它们。

However even if the performance were identical you should still use prepared statements to prevent SQL Injection. At my company this is an interview question; get it wrong and we might not hire you.

但是，即使性能相同，您仍然应该使用准备好的语句来防止 SQL 注入。在我公司，这是一个面试问题；弄错了，我们可能不会雇用您。

回答by Dan Breslau

Anecdotally: I did some experiments with prepared vs. dynamic statements using ODBC in Java 1.4 some years ago, with both Oracle and SQL Server back-ends. I found that prepared statements could be as much as 20% faster for certain queries, but there were vendor-specific differences regarding which queries were improved to what extent. (This should not be surprising, really.)

轶事：几年前，我在 Java 1.4 中使用 ODBC 对准备好的语句与动态语句进行了一些实验，同时使用 Oracle 和 SQL Server 后端。我发现对于某些查询，准备好的语句可以快 20%，但是对于哪些查询改进到什么程度，存在特定于供应商的差异。（这应该不足为奇，真的。）

The bottom line is that if you will be re-using the same query repeatedly, prepared statements may help improve performance; but if your performance is bad enough that you need to do something about it immediately, don't count on the use of prepared statements to give you a radical boost. (20% is usually nothing to write home about.)

最重要的是，如果您将重复使用相同的查询，准备好的语句可能有助于提高性能；但是如果你的表现很糟糕，你需要立即采取行动，不要指望使用准备好的语句来给你一个根本的提升。（20% 通常没什么好写的。）

Your mileage may vary, of course.

当然，您的里程可能会有所不同。

回答by Neil Coffey

The notion that prepared statements are primarily about performance is something of a misconception, although it's quite a common one.

准备好的语句主要是关于性能的概念是一种误解，尽管它很常见。

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.

另一位发帖人提到他注意到 Oracle 和 SQL Server 的速度提高了约 20%。我注意到 MySQL 有一个类似的数字。事实证明，解析查询并不是所涉及工作的重要部分。在一个非常繁忙的数据库系统上，查询解析是否会影响整体吞吐量也不清楚：总的来说，它可能只会消耗 CPU 时间，否则在数据从磁盘返回时会空闲。

So as a reason for using prepared statements, the protection against SQL injection attacks far outweighsthe performance improvement. And if you're not worried about SQL injection attacks, you probably should be...

所以作为使用预处理语句的一个原因，对 SQL 注入攻击的保护远远大于性能提升。如果你不担心 SQL 注入攻击，你可能应该......

回答by duffymo

Parsing the SQL isn't the only thing that's going on. There's validating that the tables and columns do indeed exist, creating a query plan, etc. You pay that once with a PreparedStatement.

解析 SQL 并不是唯一要做的事情。验证表和列确实存在，创建查询计划等。您使用 PreparedStatement 支付一次。

Binding to guard against SQL injection is a very good thing, indeed. Not sufficient, IMO. You still should validate input prior to getting to the persistence layer.

绑定以防止 SQL 注入确实是一件非常好的事情。不够，海事组织。在进入持久层之前，您仍然应该验证输入。

回答by Elliott Frisch

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?

这就是让我回到最初的问题的原因。这个“一些 sql” PreparedStatement 是否仍在我不知道的幕后缓存和重用？

Yes at least with Oracle. Per Oracle? Database JDBC Developer's Guide Implicit Statement Caching(emphasis added),

是的，至少对于 Oracle。每个甲骨文？Database JDBC Developer's Guide Implicit Statement Caching（强调），

When you enable implicit Statement caching, JDBC automatically caches the prepared or callable statement when you call the closemethod of this statement object. The prepared and callable statements are cached and retrieved using standard connection object and statement object methods.

Plain statements are not implicitly cached, because implicit Statement caching uses a SQL string as a key and plain statements are created without a SQL string. Therefore, implicit Statement caching applies only to the OraclePreparedStatementand OracleCallableStatementobjects, which are created with a SQL string. You cannot use implicit Statement caching with OracleStatement. When you create an OraclePreparedStatementor OracleCallableStatement, the JDBC driver automatically searches the cache for a matching statement.

当您启用隐式语句缓存时，当您调用close此语句对象的方法时，JDBC 会自动缓存准备好的或可调用的语句。使用标准连接对象和语句对象方法缓存和检索准备好的和可调用的语句。

不隐式缓存普通语句，因为隐式语句缓存使用 SQL 字符串作为键，而创建的普通语句没有 SQL 字符串。因此，隐式语句缓存仅适用于使用 SQL 字符串创建的OraclePreparedStatement和OracleCallableStatement对象。您不能对 OracleStatement 使用隐式语句缓存。创建OraclePreparedStatementor 时OracleCallableStatement，JDBC 驱动程序会自动在缓存中搜索匹配的语句。

回答by Nicola Ferraro

Prepared statements have some advantages in terms of performance with respect to normal statements, depending on how you use them. As someone stated before, if you need to execute the same query multiple times with different parameters, you can reuse the prepared statement and pass only the new parameter set. The performance improvement depends on the specific driver and database you are using.

与普通语句相比，准备好的语句在性能方面具有一些优势，具体取决于您如何使用它们。正如之前有人所说，如果您需要使用不同的参数多次执行相同的查询，您可以重用准备好的语句并只传递新的参数集。性能改进取决于您使用的特定驱动程序和数据库。

As instance, in terms of database performance, Oracle database caches the execution plan of some queries after each computation (this is not true for all versions and all configuration of Oracle). You can find improvements even if you close a statement and open a new one, because this is done at RDBMS level. This kind of caching is activated only if the two subsequent queries are (char-by-char) the same. This does not holds for normal statements because the parameters are part of the query and produce different SQL strings.

例如，在数据库性能方面，Oracle 数据库在每次计算后都会缓存一些查询的执行计划（这不适用于 Oracle 的所有版本和所有配置）。即使您关闭一个语句并打开一个新语句，您也可以发现改进，因为这是在 RDBMS 级别完成的。仅当两个后续查询 (char-by-char) 相同时，才会激活这种缓存。这不适用于普通语句，因为参数是查询的一部分并产生不同的 SQL 字符串。

Some other RDBMS can be more "intelligent", but I don't expect they will use complex pattern matching algorithms for caching the execution plans because it would lower performance. You may argue that the computation of the execution plan is only a small part of the query execution. For the general case, I agree, but.. it depends. Keep in mind that, usually, computing an execution plan can be an expensive task, because the rdbms needs to consult off-memory data like statistics (not only Oracle).

其他一些 RDBMS 可能更“智能”，但我不希望它们使用复杂的模式匹配算法来缓存执行计划，因为它会降低性能。您可能会争辩说，执行计划的计算只是查询执行的一小部分。对于一般情况，我同意，但是……这取决于。请记住，通常，计算执行计划可能是一项昂贵的任务，因为 rdbms 需要查询诸如统计信息之类的内存外数据（不仅是 Oracle）。

However, the argument about caching range from execution-plans to other parts of the extraction process. Giving to the RDBMS multiple times the same query (without going in depth for a particular implementation) helps identifying already computed structures at JDBC (driver) or RDBMS level. If you don't find any particular advantage in performance now, you can't exclude that performance improvement will be implemented in future/alternative versions of the driver/rdbms.

然而，关于缓存的争论范围从执行计划到提取过程的其他部分。多次向 RDBMS 提供相同的查询（无需深入了解特定实现）有助于在 JDBC（驱动程序）或 RDBMS 级别识别已计算的结构。如果您现在没有发现任何特别的性能优势，则不能排除在驱动程序/rdbms 的未来/替代版本中将实现性能改进。

Performance improvements for updates can be obtained by using prepared statements in batch-mode but this is another story.

通过在批处理模式下使用准备好的语句可以获得更新的性能改进，但这是另一回事。

回答by Buhake Sindi

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?

那么它对性能有什么帮助呢？驱动程序是否缓存预编译语句并在我下次执行 connection.prepareStatement 时给我一份副本？或者数据库服务器有帮助吗？

I will answer in terms of performance. Others here have already stipulated that PreparedStatements are resilient to SQL injection (blessed advantage).

我会从性能方面来回答。这里的其他人已经规定PreparedStatements 对 SQL 注入具有弹性（得天独厚的优势）。

The application (JDBC Driver) creates the PreparedStatementand passes it to the RDBMS with placeholders (the ?). The RDBMS precompiles, applying query optimization (if needed) of the received PreparedStatementand (in some) generally caches them. During execution of the PreparedStatement, the precompiled PreparedStatementis used, replacing each placeholders with their relevant values and calculated. This is in contrast to Statementwhich compiles it and executes it directly, the PreparedStatementcompiles and optimizes the query only once. Now, this scenario explained above is not an absolute case by ALL JDBC vendors but in essence that's how PreparedStatementare used and operated on.

应用程序（JDBC 驱动程序）创建PreparedStatement并将其传递给带有占位符（?）的 RDBMS 。RDBMS 预编译，应用接收的查询优化（如果需要）PreparedStatement并且（在某些情况下）通常缓存它们。在 执行期间PreparedStatement，使用预编译PreparedStatement，将每个占位符替换为其相关值并进行计算。这与Statement编译它并直接执行它相反，PreparedStatement编译和优化查询仅一次。现在，上面解释的这种情况并不是所有 JDBC 供应商的绝对情况，而是本质上是如何PreparedStatement使用和操作的。

回答by Siva Kumar

1. PreparedStatement allows you to write dynamic and parametric query

1. PreparedStatement 允许你编写动态和参数查询

By using PreparedStatement in Java you can write parametrized sql queries and send different parameters by using same sql queries which is lot better than creating different queries.

通过在 Java 中使用 PreparedStatement，您可以编写参数化的 sql 查询并使用相同的 sql 查询发送不同的参数，这比创建不同的查询要好得多。

2. PreparedStatement is faster than Statement in Java

2. PreparedStatement 比 Java 中的 Statement 更快

One of the major benefits of using PreparedStatement is better performance. PreparedStatement gets pre compiled In database and there access plan is also cached in database, which allows database to execute parametric query written using prepared statement much faster than normal query because it has less work to do. You should always try to use PreparedStatement in production JDBC code to reduce load on database. In order to get performance benefit its worth noting to use only parametrized version of sql query and not with string concatenation

使用 PreparedStatement 的主要好处之一是更好的性能。PreparedStatement 被预编译在数据库中，并且访问计划也缓存在数据库中，这允许数据库执行使用预编译语句编写的参数查询比普通查询快得多，因为它需要做的工作更少。您应该始终尝试在生产 JDBC 代码中使用 PreparedStatement 以减少数据库负载。为了获得性能优势，值得注意的是只使用参数化版本的 sql 查询而不是字符串连接

3. PreparedStatement prevents SQL Injection attacks in Java

3. PreparedStatement 防止 Java 中的 SQL 注入攻击

Read more: http://javarevisited.blogspot.com/2012/03/why-use-preparedstatement-in-java-jdbc.html#ixzz3LejuMnVL

阅读更多：http: //javarevisited.blogspot.com/2012/03/why-use-preparedstatement-in-java-jdbc.html#ixzz3LejuMnVL

回答by nybon

Short answer:

简短的回答：

PreparedStatement helps performance because typically DB clients perform the same query repetitively, and this makes it possible to do some pre-processingfor the initial query to speed up the following repetitive queries.

PreparedStatement 有助于提高性能，因为通常 DB 客户端会重复执行相同的查询，这使得可以对初始查询进行一些预处理以加快后续重复查询的速度。

Long answer:

长答案：

According to Wikipedia, the typical workflow of using a prepared statement is as follows:

根据维基百科，使用准备好的语句的典型工作流程如下：

Prepare: The statement template is created by the application and sent to the database management system (DBMS). Certain values are left unspecified, called parameters, placeholders or bind variables (labelled "?" below): INSERT INTO PRODUCT (name, price) VALUES (?, ?)

(Pre-compilation): The DBMS parses, compiles, and performs query optimization on the statement template, and stores the result without executing it.

Execute: At a later time, the application supplies (or binds) values for the parameters, and the DBMS executes the statement (possibly returning a result). The application may execute the statement as many times as it wants with different values. In this example, it might supply 'Bread' for the first parameter and '1.00' for the second parameter.

Prepare：语句模板由应用程序创建并发送到数据库管理系统（DBMS）。某些值未指定，称为参数、占位符或绑定变量（下面标记为“？”）： INSERT INTO PRODUCT (name, price) VALUES (?, ?)

(Pre-compilation)：DBMS对语句模板进行解析、编译、查询优化，不执行就存储结果。

执行：稍后，应用程序为参数提供（或绑定）值，然后 DBMS 执行语句（可能返回结果）。应用程序可以根据需要使用不同的值多次执行该语句。在此示例中，它可能为第一个参数提供“面包”，为第二个参数提供“1.00”。

Prepare:

准备：

In JDBC, the "Prepare" step is done by calling java.sql.Connection.prepareStatement(String sql) API. According to its Javadoc:

在 JDBC 中，“准备”步骤是通过调用 java.sql.Connection 完成的。prepareStatement(String sql) API。根据其 Javadoc：

This method is optimized for handling parametric SQL statements that benefit from precompilation. If the driver supports precompilation, the method prepareStatement will send the statement to the database for precompilation. Some drivers may not support precompilation. In this case, the statement may not be sent to the database until the PreparedStatement object is executed. This has no direct effect on users; however, it does affect which methods throw certain SQLException objects.

此方法针对处理受益于预编译的参数 SQL 语句进行了优化。如果驱动支持预编译，prepareStatement 方法会将语句发送到数据库进行预编译。某些驱动程序可能不支持预编译。在这种情况下，在执行 PreparedStatement 对象之前，可能不会将语句发送到数据库。这对用户没有直接影响；但是，它确实会影响哪些方法会抛出某些 SQLException 对象。

Since calling this API may send the SQL statement to database, it is an expensive call typically. Depending on JDBC driver's implementation, if you have the same sql statement template, for better performance, you may have to avoiding calling this API multiple times in client side for the same sql statement template.

由于调用此 API 可能会将 SQL 语句发送到数据库，因此它通常是一个昂贵的调用。根据 JDBC 驱动的实现，如果你有相同的 sql 语句模板，为了更好的性能，你可能不得不避免在客户端多次调用这个 API 相同的 sql 语句模板。

Precompilation:

预编译：

The sent statement template will be pre-compiled on databaseand cached in db server. The database will probably use the connection and sql statement template as the key, and the pre-compiled query and the computed query plan as value in the cache. Parsing query may need to validate table, columns to be queried, so it could be an expensive operation, and computation of query planis an expensive operation too.

发送的语句模板将在数据库上预编译并缓存在数据库服务器中。数据库可能会使用连接和sql语句模板作为键，预编译查询和计算出的查询计划作为缓存中的值。解析查询可能需要验证表，要查询的列，所以它可能是一个昂贵的操作，查询计划的计算也是一个昂贵的操作。

Execute:

执行：

For following queries from the same connection and sql statement template, the pre-compiled query and query plan will be looked up directly from cache by database server without re-computation again.

对于来自相同连接和sql语句模板的后续查询，数据库服务器将直接从缓存中查找预编译的查询和查询计划，无需再次重新计算。

Conclusion:

结论：

From performance perspective, using prepare statement is a two-phase process:

从性能的角度来看，使用prepare语句是一个两阶段的过程：

1. Phase 1, prepare-and-precompilation, this phase is expected to be done once and add some overhead for the performance.
2. Phase 2, repeated executions of the same query, since phase 1 has some pre processing for the query, if the number of repeating query is large enough, this can save lots of pre-processing effort for the same query.

1. 阶段 1，准备和预编译，这个阶段预计只做一次，并为性能增加一些开销。
2. 阶段2，重复执行同一个查询，由于阶段1对查询进行了一些预处理，如果重复查询的次数足够大，可以为同一个查询节省大量的预处理工作。

And if you want to know more details, there are some articles explaining the benefits of PrepareStatement:

如果你想了解更多细节，有一些文章解释了 PrepareStatement 的好处：

1. http://javarevisited.blogspot.com/2012/03/why-use-preparedstatement-in-java-jdbc.html
2. http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

1. http://javarevisited.blogspot.com/2012/03/why-use-preparedstatement-in-java-jdbc.html
2. http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html