澄清Java / SQLite批处理和自动提交 [英] Clarification of Java/SQLite batch and auto-commit

问题描述

我从SQLite Java库网站复制了以下示例：

  PreparedStatement prep = 
 conn.prepareStatement（ 插入人的价值观（？，？）;）; 
 prep.setString（1，Gandhi）; 
 prep.setString（2，politics）; 
 prep.addBatch（）; 
 prep.setString（1，图灵）; 
 prep.setString（2，computers）; 
 prep.addBatch（）; 
 conn.setAutoCommit（false）; 
 prep.executeBatch（）; 
 conn.setAutoCommit（true）; 
  

我很难理解切换 autoCommit（）的重要性 executeBatch（）的任一侧。它是否仅仅阻止了对每个批处理操作的提交？因此，单个批量提交将由 setAutoCommit（true）进行。

解决方案

自动提交在批处理前被禁用，因为启用自动提交将提交（即等待同步发生，这意味着它将在插入的每一行之后等待数据实际写入持久存储，如硬盘）。 / p>

如果自动提交为假，它将不会等待同步。

等待同步的差异而不是wait是保证数据实际上是硬盘还是缓冲区（可以是缓冲IO或硬盘缓冲区）。

In简而言之，禁用自动提交可以提高性能。我认为默认情况下会启用自动提交。

另一种优化方式

如果您想要自动提交并仍然需要性能提升，请尝试在批处理操作之前启动事务然后提交交易。这样sqlite在每次插入后都不会自动提交，会提高性能。

编辑：

当您开始交易时，您只能为该交易禁用自动提交
，并且一旦交易结束，它将再次开启。当您单独插入/更新行时（而不是批处理），自动提交有助于
，那么您不必为每次插入/更新显式启动
事务。关于将auto-commit设置为true，在
之后，不会调用commit。如果你使自动提交
为true，那么你已插入/更新的任何内容都不会产生任何影响，并且在进行那些插入/更新之前不会像auto-commit那样保持相同的
保证。

​​以下是有关加速Sqlite INSERT的一些信息。

I copied the following example from an SQLite Java library website:

PreparedStatement prep =
         conn.prepareStatement("insert into people values (?, ?);");
prep.setString(1, "Gandhi");
prep.setString(2, "politics");
prep.addBatch();
prep.setString(1, "Turing");
prep.setString(2, "computers");
prep.addBatch();
conn.setAutoCommit(false);
prep.executeBatch();
conn.setAutoCommit(true);

I'm struggling to understand the significance of toggling autoCommit() either side of executeBatch(). Does it merely prevent a commit being made for each individual batch operation? Thus a single 'bulk' commit is will be made by setAutoCommit(true).

解决方案

The auto commit is disabled before batch because enabling auto commit will commit (i.e. wait for sync to happen which means it will wait the data is actually written to persistent storage like hard disk) after every row that is inserted.

If auto commit is false, it will not wait for sync.

The difference in waiting for sync and not waiting is the guaranty that whether data is actually to hard disk or it is in the buffer (that could be buffered IO or buffer of hard disk).

In short, disabling auto commit gives you performance boost. And I think by default auto commit is enabled.

Another way of optimization

If you want to have auto commit ON and still need performance boost just try to start as transaction before the batch operation and commit the transaction after. This way sqlite wont auto commit after every insert and it will give good performance boost.

EDIT:

When you starting a transaction you are only disabling auto commit for that transaction and it will be again 'on' once transaction is over. What auto commit helps is when you are inserting/updating rows separately (not as batch), then you dont have to start a transaction explicitly for every insert/update. And regarding setting auto-commit to true, after the fact, does not do call for commit. If you make auto-commit true and whatever you have already inserted/updated wont have any effect and won't have same guaranties as auto-commit true prior to making those insert/update.

Here's some information about speeding up Sqlite INSERTs.

这篇关于澄清Java / SQLite批处理和自动提交的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！