postgresql jdbc 批处理性能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12639118/
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
jdbc batch performance
提问by wutzebaer
i'm batching updates with jdbc
我正在用 jdbc 批量更新
ps = con.prepareStatement("");
ps.addBatch();
ps.executeBatch();
but in the background it seems, that the prostgres driver sends the query bit by bit to the database.
但在后台，prostgres 驱动程序似乎将查询一点一点地发送到数据库。
org.postgresql.core.v3.QueryExecutorImpl:398
org.postgresql.core.v3.QueryExecutorImpl:398
for (int i = 0; i < queries.length; ++i)
{
V3Query query = (V3Query)queries[i];
V3ParameterList parameters = (V3ParameterList)parameterLists[i];
if (parameters == null)
parameters = SimpleQuery.NO_PARAMETERS;
sendQuery(query, parameters, maxRows, fetchSize, flags, trackingHandler);
if (trackingHandler.hasErrors())
break;
}
is there a possibility to let him send 1000 a time to speed it up?
有没有可能让他一次送1000个加速？
回答by Craig Ringer
AFAIK is no server-side batching in the fe/be protocol, so PgJDBC can't use it.. Update: Well, I was wrong. PgJDBC (accurate as of 9.3) doessend batches of queries to the server if it doesn't need to fetch generated keys. It just queues a bunch of queries up in the send buffer without syncing up with the server after each individual query.
AFAIK 在fe/be 协议中没有服务器端批处理，所以 PgJDBC 不能使用它。. 更新：嗯，我错了。PgJDBC（准确的9.3），并发送查询的批量服务器，如果它不需要取生成的密钥。它只是在发送缓冲区中排队一堆查询，而不在每个单独的查询之后与服务器同步。
See:
看：
- Issue #15: Enable batching when returning generated keys
- Issue #195: PgJDBC does not pipeline batches that return generated keys
Even when generated keys are requested the extended query protocolis used to ensure that the query text doesn't need to be sent every time, just the parameters.
即使在请求生成的键时，扩展查询协议也用于确保不需要每次都发送查询文本，只需发送参数。
Frankly, JDBC batching isn't a great solution in any case. It's easy to use for the app developer, but pretty sub-optimal for performance as the server still has to execute every statement individually - though not parseand planthem individually so long as you use prepared statements.
坦率地说，JDBC 批处理在任何情况下都不是一个很好的解决方案。对于应用程序开发人员来说，它很容易使用，但对于性能来说却是次优的，因为服务器仍然必须单独执行每个语句 - 尽管只要您使用准备好的语句，就不会单独解析和计划它们。
If autocommit is on, performance will be absolutely pathetic because each statement triggers a commit. Even with autocommit off running lots of little statements won't be particularly fast even if you could eliminate the round-trip delays.
如果启用自动提交，性能将非常糟糕，因为每个语句都会触发一次提交。即使关闭自动提交，运行大量小语句也不会特别快，即使您可以消除往返延迟。
A better solution for lots of simple UPDATEs can be to:
许多简单UPDATEs 的更好解决方案可以是：
COPYnew data into aTEMPORARYorUNLOGGEDtable; and- Use
UPDATE ... FROMtoUPDATEwith aJOINagainst the copied table
COPY将新数据放入TEMPORARY或UNLOGGED表中；和- 使用
UPDATE ... FROMtoUPDATEwith aJOIN反对复制的表
For COPY, see the PgJDBC docsand the COPYdocumentation in the server docs.
对于抄见的PgJDBC文档，并在COPY服务器中的文档文件。
You'll often find it's possible to tweak things so your app doesn't have to send all those individual UPDATEs at all.
您经常会发现可以进行一些调整，这样您的应用程序就不必发送所有这些个人信息UPDATE了。
