Monday, March 18, 2024
HomeProductsDelphi DACIncreasing SQLite Performance

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

Microsoft Windows 7 x86
DEFAULT FILE MEMORY
235 225 215

 

MacOS X 10.9 Mavericks
DEFAULT FILE MEMORY
34 33 32

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

Microsoft Windows 7 x86
DELETE TRUNCATE PERSIST MEMORY OFF
235 210 220 65 63

 

MacOS X 10.9 Mavericks
DELETE TRUNCATE PERSIST MEMORY OFF
34 4 3 2 1

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

Microsoft Windows 7 x86
FULL NORMAL OFF
235 175 43

 

MacOS X 10.9 Mavericks
FULL NORMAL OFF
34 32 32

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

Microsoft Windows 7 x86
NORMAL EXCLUSIVE
235 155

 

MacOS X 10.9 Mavericks
NORMAL EXCLUSIVE
34 3

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

Microsoft Windows 7 x86
0 500 1000 2000 4000 8000 10000
222 185 230 230 230 230 250

 

MacOS X 10.9 Mavericks
0 500 1000 2000 4000 8000 10000
34 34 34 34 34 34

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

Windows 7 x86
512 1024 2048 4096 8192 16384 32768 65535
240 235 227 225 255 295 450 295

 

MacOS X 10.9 Mavericks
512 1024 2048 4096 8192 16384 32768 65535
34 34 33 33 34 40 50 65

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.
RELATED ARTICLES

8 COMMENTS

  1. Hello – nice article. But when I see the measured values I have one question: Why is windows so horrible slow? Can this be fixed or relies this on the poor performance of ntfs?

  2. Hello, Sommer!
    This article is not intended to compare SQLite performance on Microsoft Windows and MacOS X platforms, but to show SQLite certain settings impact when using a particular platform. In addition, in our test environment computers with Microsoft Windows and MacOS X have completely different hardware implementation.
    Therefore, it is appropriate not to compare Microsoft Windows and MacOS X characteristics, but to compare characteristics of the same platform with different settings.

  3. Nice article, thanks!

    One question: which of these parameter will still affect data loading done using the “.import” command within the sqlite3?
    For instance, I am assuming that blocking of DML statements is already done when using “.import”.
    But does setting “LOCKING_MODE” make any difference?

    • Hello, G!
      You mean the option .import Command Line Shell For SQLite. In this article, we considered the influence of SQLite settings using PRAGMA commands on work performance with data. Possibly, Command Line Shell For SQLite implements the data import operation with these settings. Or these settings can be applied to SQLite DB when using this utility. You can find the answers to these questions by referring to this utility documentation or test its work by yourself.

  4. Hi Thanks for the article
    i have one issue that while using WAL PRAGMA_Mode the no of .shm files are going out of bounds.how can i limit it?

    • Hello, Paresh!
      Working with “X-wal” and “X-shm” files in WAL-mode fully depends on the work of the SQLite Engine itself and we cannot influence this behavior in our access components for this DB.
      Try contacting SQLite technical support with this question or find the answer at one of the specialized forums.

Comments are closed.

Whitepaper

Social

Topics

Products