Copy SQLite database from hard disk to RAM and back?
lazarus / 16-Databases

----------------------------------------------------------------------
stem · 2014-07-10 05:58

Hi,

I've created an application. The workflow goes like: open SQLite
database from hard disk, work with it for several minutes, close
database. Every INSERT statement is put in a transaction which is
executed immediately so there are no journal files on the disk.

Everything works fine but: There is relatively heavy traffic on the
hard disk. It becomes worse when I synchronize the directory with
Dropbox.

Would it be a suitable optimization if I copy the database from hard
disk to RAM (with "ATTACH"?), work with it completely in RAM and save
it back at the end? Does anyone have experience with doing that? Are
there code examples?

Would it also be possible to put the complete workflow in just one
tranaction which is executed at the end? I don't know if that would be
better because Dropbox would have to synchronize permanently the
journal file ...


Thank you in advance for your suggestions 

stem

----------------------------------------------------------------------
marcov · 2014-07-10 08:12

The dropbox sharing will always be slowing. A first step is to do the
inserting elsewhere, and copy it back to the dropbox dir to sync.

You might want to check if the sqlite driver has caching or atomicity
options. That might allow it to combine some writes that are
individual writes now, while still being safe if you close the driver
properly

----------------------------------------------------------------------
Leledumbo · 2014-07-10 08:22

Quote from: marcov on July 10, 2014, 10:12:34 amYou might want to
check if the sqlite driver has caching or atomicity options. That
might allow it to combine some writes that are individual writes now,
while still being safe if you close the driver properly
Seems so:
http://sqlite.org/lang_transaction.html
http://sqlite.org/atomiccommit.html
http://sqlite.org/transactional.html