CREATE INDEX - :memory: vs file

Discussion of open issues, suggestions and bugs regarding LiteDAC (SQLite Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
ProvalisResearch
Posts: 7
Joined: Tue 25 Jul 2017 13:53

CREATE INDEX - :memory: vs file

Post by ProvalisResearch » Wed 26 Jul 2017 15:06

Hello,

After populating my table using all that I have read to make it as fast as possible (PRAGMA tweaking, batch insert, etc.), I then create my index (which is way faster than creating the index before populating my table).

My PRAGMA tweaks are:
FLiteConnection.SQL.SQL.Add('PRAGMA CACHE_SIZE=500;');
FLiteConnection.SQL.SQL.Add('PRAGMA JOURNAL_MODE=OFF;');
FLiteConnection.SQL.SQL.Add('PRAGMA LOCKING_MODE=EXCLUSIVE;');
FLiteConnection.SQL.SQL.Add('PRAGMA PAGE_SIZE=4096;');
FLiteConnection.SQL.SQL.Add('PRAGMA SYNCHRONOUS=OFF;');
FLiteConnection.SQL.SQL.Add('PRAGMA TEMP_STORE=MEMORY;');

Since PRAGMA SYNCHRONOUS=OFF, the file is not updated immediately on disk.

If I create my index at this point, it is slow.

One massive performance boost to the indexing is to close the connection before creating the index. I guess it tells the OS to write it to the file. It works really well.

The problem is that if my database = ‘:memory:’, closing the connection deletes the database from memory.

Is there any other way other than closing the connection to force the data to disk?

Thank you.

MaximG
Devart Team
Posts: 1030
Joined: Mon 06 Jul 2015 11:34

Re: CREATE INDEX - :memory: vs file

Post by MaximG » Thu 27 Jul 2017 11:57

We investigated the work of LiteDAC according to your description. Unfortunately, we could not detect the difference in the speed of our components when creating an index with the pre-closing connection to the database and without it. To further investigate the issue, please make up and send us a small example that clearly demonstrates this difference. You can send this example via the e-support form (https://www.devart.com - the menu "Support" \ "Request Support")

ProvalisResearch
Posts: 7
Joined: Tue 25 Jul 2017 13:53

Re: CREATE INDEX - :memory: vs file

Post by ProvalisResearch » Wed 23 Aug 2017 17:58

Sorry for the delay, I was on vacation.

As requested, I sent you a project that reproduces the issue.

Looking forward to your response.

Thank you for your time.

MaximG
Devart Team
Posts: 1030
Joined: Mon 06 Jul 2015 11:34

Re: CREATE INDEX - :memory: vs file

Post by MaximG » Mon 28 Aug 2017 14:29

We investigated the sent project. The described behavior is not related to our components functionality, but to specificity of SQLite functionality. You can make sure in it by using standard access components in your project, included in RAD Studio (FireDAC).

Post Reply