Devart Blog

PRAGMA tweaking

Increasing SQLite Performance

Posted by on December 21st, 2015

One the major issues a developer encounters when using the SQLite DBMS in his applications is its performance issue.

Perhaps, a classic case everyone gets into when using SQLite for the first time is very slow execution of multiple INSERT/UPDATE/DELETE operations. Indeed, sequential executions of not even thousands, but hundreds of INSERTs into a table may take too long.
The origin of the issue lies in the specificity of using transactions in SQLite. SQLite starts a transaction automatically every time before any DML statement execution and commits it after execution. Accordingly, when executing multiple consequent statements, a new transaction will be started and committed for each statement.

The solution of this problem is quite simple — the block of DML statements may be enclosed into BEGIN … END operators block ( http://www.sqlite.org/lang_transaction.html ). In this case, each DML statement won’t be executed in a separate transaction, but a single transaction will be started before the whole block execution and committed after all modifications.

Such an approach increases SQLite data modification performance by times. See more details about it in the SQLite documentation ( http://www.sqlite.org/faq.html#q19 ).

However, this approach is not the only way to increase performance in SQLite. Parameters of the DBMS may also be configured using so-called PRAGMA ( http://www.sqlite.org/pragma.html ). The fact is that SQLite parameters are oriented not to high performance by default, but to maximum data safety and integrity. Modification of these parameters may increase performance, however, note, that the data corruption risks increase too.

Let’s analyze the impact to inserts performance by different PRAGMAs using LiteDAC. (more…)