JDBC批处理性能 [英] jdbc batch performance

问题描述

我是用JDBC批处理更新

i'm batching updates with jdbc

ps = con.prepareStatement("");
ps.addBatch();
ps.executeBatch();

，但在背景看来，该prostgres驾驶员通过位发送查询位到数据库。

but in the background it seems, that the prostgres driver sends the query bit by bit to the database.

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;
            }

有可能让他送1000的时间来加快步伐？

is there a possibility to let him send 1000 a time to speed it up?

推荐答案

AFAIK是在的 FE / BE协议，所以PgJDBC不能使用它。。的更新的：好吧，我错了。 PgJDBC（准确的9.3）的确实的查询发送批次到服务器的如果不需要取生成的密钥的。它只是排队一堆发送缓冲区查询起来不每一个人的查询后，同步与服务器。

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) does send 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.

请参阅：

• 问题＃15：返回生成的密钥时，启用配料


• 问题＃195：PgJDBC这是否返回生成的密钥不是流水线批量

• 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 protocol is used to ensure that the query text doesn't need to be sent every time, just the parameters.

坦率地说，JDBC批处理并非在任何情况下很好的解决方案。它易于使用的应用程序开发，但pretty次优的性能，服务器仍然有单独执行每个语句 - 虽然不是的解析<​​/ em>的和的计划的他们个别只要你使用prepared声明。

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 parse and plan them individually so long as you use prepared statements.

如果是自动提交的，因为每个语句触发提交的性能是绝对可怜。即使关闭自动提交运行很多小的语句不会特别快，即使你可以消除往返延迟。

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.

有很多简单的更新的更好的解决方案 s时，可以是：

A better solution for lots of simple UPDATEs can be to:

• 复制新的数据到临时或未登录表;和


• 使用更新... FROM 到更新与加入对抄表

• COPY new data into a TEMPORARY or UNLOGGED table; and
• Use UPDATE ... FROM to UPDATE with a JOIN against the copied table

有关副本，请参阅的PgJDBC文档和的<$在C $ C>复制文档服务器文档。

For COPY, see the PgJDBC docs and the COPY documentation in the server docs.

您会经常发现有可能调整的事情，所以您的应用程序不必将所有这些个人更新■在所有。

You'll often find it's possible to tweak things so your app doesn't have to send all those individual UPDATEs at all.

这篇关于JDBC批处理性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！