CREATE INDEX - :memory: vs file
Posted: 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.
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.