CREATE INDEX - :memory: vs file

[ProvalisResearch]

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]

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]

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]

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).

[dhirajraval]

Portable gaming causes them to feel loose, so they return to it to ease pressure. Games on portable are an effective method for relaxing while at the same time sitting tight for different exercises. Furthermore, mobile game development company in india utilize versatile games to socially interface with different players.