Increasing SQLite Performance

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 ( https://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 (https://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 (https://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.

We will use a test table SPEED_TEST in our project:

CREATE TABLE SPEED_TEST (
  ID INTEGER,
  F_INTEGER INTEGER,
  F_FLOAT FLOAT,
  F_STRING VARCHAR(250),
  F_DATE DATETIME,
  CONSTRAINT PK_BATCH_TEST PRIMARY KEY (ID)
)

In each test, we will delete the database and re-create it, and then insert 10,000 records to the SPEED_TEST table as follows:

var
  LiteQuery:  TLiteQuery;
  i: Integer;
  ParamID, ParamF_INTEGER, ParamF_FLOAT, ParamF_STRING, ParamF_DATE: TDAParam;
begin
   ...
  LiteQuery.SQL.Text := 'INSERT INTO SPEED_TEST VALUES (:ID, :F_INTEGER, :F_FLOAT, :F_STRING, :F_DATE)';  ParamID := LiteQuery.Params.ParamByName('ID');
  ParamID.DataType := ftInteger;
  ParamF_INTEGER := LiteQuery.Params.ParamByName('F_INTEGER');
  ParamF_INTEGER.DataType := ftInteger;
  ParamF_FLOAT := LiteQuery.Params.ParamByName('F_FLOAT');
  ParamF_FLOAT.DataType := ftFloat;
  ParamF_STRING := LiteQuery.Params.ParamByName('F_STRING');
  ParamF_STRING.DataType := ftWideString;
  ParamF_DATE := LiteQuery.Params.ParamByName('F_DATE');
  ParamF_DATE.DataType := ftDateTime;

  for i := 0 to 10000 - 1 do begin
    ParamID.AsInteger := i + 1;
    ParamF_INTEGER.AsInteger := i + 5000 + 1;
    ParamF_FLOAT.AsFloat := (i + 1) / 15;
    ParamF_STRING.AsString := 'Values ' + IntToStr(i + 1);
    ParamF_DATE.AsDateTime := Now;
    LiteQuery.Execute;
  end;
  ...
end;

We’ll run the test project on 2 platforms: Microsoft Windows 7 x86 and MacOS X 10.9 Mavericks.

PRAGMA TEMP_STORE

This parameter allows to specify location of temporary objects in the database: tables, indexes, triggers, views, etc. PRAGMA TEMP_STORE accepts 3 values:

• 0 | DEFAULT — the default value, when location of temporary objects storage is defined by the option, which is set during SQLite compilation;
• 1 | FILE — temporary objects are stored in a file (its location depends on the used OS);
• 2 | MEMORY — temporary objects are stored in memory.

When the TEMP_STORE parameter is changed, all the temporary tables, indexes, triggers, views are deleted.

Time: sec

According to the retrieved results, making RAM a storage for temporary DB objects increases performance a little.

PRAGMA JOURNAL_MODE

The parameter sets the database log working mode (rollback journal file used on transaction processing).

PRAGMA JOURNAL_MODE accepts the following values:

• DELETE (the default value) — in this mode the log file is deleted after transaction is committed.
• TRUNCATE — after transaction is committed, the log file is truncated to 0 size. This works faster than log deleting on a number of platforms, since the catalog containing the log file is not modified at this.
• PERSIST — instead of deleting and truncating, the log file header is filled in with zeros. In this case, the log file size doesn’t change and it can require quite much space. However, such an operation may be executed faster than DELETE or TRUNCATE.
• MEMORY — the rollback journal is kept in RAM and doesn’t use the disk subsystem. Such mode provides more significant performance increase when working with log. However, in case of any failures within a transaction, data in the DB will be corrupted with high probability due to a lack of saved data copy on the disk.
• OFF — not using log. In this mode, transaction rollback is impossible. In case of a crash, the database will be likely corrupted.

Time: sec

Changing this parameter significantly increases performance when inserting data on both platforms. Note, that at using MEMORY or OFF values, the risk of data loss is maximal too.

PRAGMA SYNCHRONOUS

Defines the mode of rollback journal synchronization with the data.

• 2 | FULL — the default value. SQLite pauses working in critical cases, in order to guarantee 100% data integrity when saving to the database file — even on system crash or power failure. This synchronization mode is absolutely safe, but the most slow.
• 1 | NORMAL — SQLite will pause in critical cases as well, but less often in the FULL mode. At this value, there is a (quite small) data corruption possibility. The NORMAL mode is a compromise between reliability and performance.
• 0 | OFF — database synchronization is not used. I.e., SQLite takes no breaks when transmitting data to the operating system. Such mode can substantially increase performance. The database will meet the integrity conditions after the SQLite crash, however, data will be corrupted in case of system crash or power off.

Time: sec

The test demonstrated significant performance increase on Windows. It is highly recommended to speed up performance this way only with assurance of the operating system stability and power quality.

PRAGMA LOCKING_MODE

Defines the locking mode.

• NORMAL — the default value. The database file is locked at the moment of record reading or writing. After the operation completes, the lock is released.
• EXCLUSIVE — the database file is used in exclusive mode. The number of system calls to implement file operations decreases in this case, which may increase database performance.

Time: sec

The most secure approach to increase performance. Though, EXCLUSIVE MODE allows the database to serve only one connection.

PRAGMA CACHE_SIZE

Defines the number of pages from the database file for storing in RAM, i.e., the cache size. Increasing this parameter may increase performance of the database on high load, since the greater its value is, the more modifications a session can perform before retrieving exclusive lock.

Time: sec

Performance increase is observed only on Windows. Changing this parameter is almost secure, yet the performance growth is minor.

PRAGMA PAGE_SIZE

Defines the database page size.

The page size is set by default depending on some computer and OS specifications. They include disk sector size and the used encoding. SQLite supports page size range from 512 to 65536 bytes.

Time: sec

Like the previous parameter, PRAGMA PAGE_SIZE is almost safe with respect to risk of data corruption, however, tuning of this parameter doesn’t lead to significant performance increase and requires search for values depending on the used sector size.

Summary

1. There are ways to increase SQLite data insert performance in addition to using transactions.
2. Usage of PRAGMA may significantly increase performance of SQLite.
3. SQLite default settings don’t provide optimal performance, yet guarantee absolute data integrity and safety.
4. Changing SQLite parameter values in favor of performance increases the risk of data corruption.
5. Values of some parameters (PRAGMA PAGE_SIZE, PRAGMA CACHE_SIZE) should be found depending on specifications of the used environment.