SQLite: Copy database to memory and back?

[stem]

Hi,

I have an application which uses SQLite. Every update command etc. is instantly written to file, everything works.

Now I wonder if it would make more sense to copy the database from file to memory at the beginning (of the program execution), then working only in memory, and at the end (of program execution) writing the database back from memory to file.

Another advantage would be that synchronizing software like Dropbox etc. would synchronize this file only at the end and not with every SQL update command (because I'm planning to keep the database file synchronized in future).

What do you think? Would it make sense to change my application in the way I described?

Are there free pascal examples of moving the database "back and forth"?

Thank you! 


stem

[GAN]

I think more simple solution may be make a copy of de database, work with this copy, then delete de source database and rewrite the copy.

[mangakissa]

Another advantage would be that synchronizing software like Dropbox etc. would synchronize this file only at the end and not with every SQL update command (because I'm planning to keep the database file synchronized in future).

First of all: Dropbox isn't a remote drive, but a backupdrive to backup files. 90% of people using dropbox the wrong way. Just work with the original file and backup it in a backup program (for example syncback free) in a schedule or build a copy in your application to dropbox.

Now I wonder if it would make more sense to copy the database from file to memory at the beginning (of the program execution), then working only in memory, and at the end (of program execution) writing the database back from memory to file.

Second of all: How many table are you using? Normally when a table is open (SQLDB / ZEOS) you are working in memory.

[miab3]

@stem,

If you care about the speed, then for ZEOS (7.2 or 7.3) use this connection for SQLite:

Code: Pascal  [Select][+][-]

1. ZConnection1.Connected:=false;
2. ZConnection1.Properties.Add('synchronous=0');
3. ZConnection1.Properties.Add('locking_mode=EXCLUSIVE');                                              
4. ZConnection1.Connected:=true;
5. ZConnection1.ExecuteDirect('PRAGMA journal_mode=MEMORY');

Then you practically act in memory.
https://www.sqlite.org/pragma.html

Michal

[mangakissa]

And how about the inserts / updates/ deletes in real time?