SQLite：结束事务需要的时间太长 [英] SQLite: End transaction is taking too long

问题描述

我在事务中插入了几行。但是当我做一个'END TRANSACTION'它花费大约250ms执行，而'BEGIN TRANSACTION'几乎不需要大约1ms。我需要在这里固定速度来适应我的应用程序。我如何？

I am inserting a few rows with in a transaction. But when I do a 'END TRANSACTION' its taking around 250ms to execute while the 'BEGIN TRANSACTION' hardly takes around 1ms. I need to fasten the speed here to suite my application. How can I?

[edit]
*单个线程正在访问数据库。
*我在这个数据库中有两个表，这两个表都有主键。
*在事务中，每个表中只有一个插入。
* OS - windows 7

[edit] * A single thread is accessing the database. * I have 2 tables in this database and both these have primary key on them. * With in a transaction there is exactly one insert into each the tables. * OS - windows 7

推荐答案

使用开箱即用的默认设置sqlite，提交事务有意义。这是由于sqlite如何提交您的事务。

Using the out of the box, or default, settings of sqlite, 250ms to commit a transaction makes sense. This is due to how sqlite commits your transaction. It waits for the VFS for a guarantee that the writes are committed to disk to return.

这里有几个优化的可能性。

Here are a couple of possibilities to optimize.

如果可能，每个事务执行更多的插入。尝试在一个事务中运行，比如说100个插入，并且看到与仅有1个插入相比有多少差别。你可能甚至看不到任何（即100插入可能会略微超过250ms）。

If possible, execute more inserts per transaction. Try running, say, 100 inserts in one transactions, and see how little difference there will be compared to only 1 insert. You may not even see any (i.e. 100 inserts will probably take marginally more than 250ms).

底线是，你会得到更多的爆炸，

Bottom line is that you'll get more bang for your buck as each insert will ultimately take less time (on average).

我强烈建议您尝试WAL日志记录因为你应该看到从250ms的戏剧性减少。 WAL不应该比常规日记安全性低。 WAL的速度更快的原因是它的名字：它附加到日志文件，而不是让数据库文件吸收你的提交的更改每次提交。请阅读此全文。

I strongly recommend you try WAL journaling as you should see a dramatic reduction from 250ms. WAL shouldn't be any less safe than regular journaling. The reason WAL is faster is in its name: it appends to a journaling file instead of having the database file absorb the changes of your commit every time you commit. Read this for the full story.

要激活WAL日志记录，请将 journal_mode pragma设置为 WAL ：

To activate WAL journaling, set the journal_mode pragma to WAL:

PRAGMA journal_mode = WAL;

更改同步pragma

或者可能不够好为你，因为它不太安全。因此，我推荐它，只有当你明白什么是风险，以及如果前两个建议对你不够好，或者如果你不能使用它们。

Change the synchronous pragma

This may or may not be good enough for you as it is less safe. As such, I recommend it only if you understand what the risks are, and also if the two previous suggestions are not good enough for you, or if you cannot use them.

基本上，将同步 pragma 更改为NORMAL或OFF将导致sqlite不等待VFS以保证

Basically, changing the synchronous pragma to NORMAL or OFF will cause sqlite to not wait after the VFS for a guarantee that the writes are committed to disk to return.

请阅读文档，那么如果您仍然想要尝试，可以将您的编译指示设置为OFF或NORMAL：

Please read the documentation first, then if you still want to try it, you can set your pragma to either OFF or NORMAL:

PRAGMA synchronous = NORMAL;

或

PRAGMA synchronous = OFF;

这篇关于SQLite：结束事务需要的时间太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！