SQLite Databases - what's buffered in memory and what isn't

Databases
1

In order to optimise performance I’m trying to understand what is kept in memory and what isn’t, and when what is kept in memory is written to disk.

I can’t find it explicitly explained in Xojo documentation.

First - in memory SQlite database

  1. If I have a SQLite in memory database, can I load it from an on disk database to start with. And how? Do I then have two databases that are automatically kept synchronised.

  2. When I amend or insert a record, when is that written to disk
    a) On each change?
    b) Only when I tell it (and how - is that by commit or backup?)
    c) Only when I close it?
    d) It doesn’t happen at all
    e) other?

Second - SQLite on disk database (single user)

Is the full resulting content of all the rows a RowSet kept in memory immediately upon doing a SelectSQL or is each row read in from disk as it is accessed by “Each Row As DatabaseRow” or even just when row.column is referenced.

If it is all kept in memory, is that kept there until the RowSet goes out of scope or is explicitly .close-d

If it is kept in memory, then presumably as a RowSet is an object it can be kept live in a Static for the duration until the Database is closed.

Thanks for anyone who can give me the answers

2

You may want to set cache to a big size to avoid it reading from disk a lot.

See blog post:
https://www.mbs-plugins.de/archive/2017-10-16/Big_SQLite_Cache_for_64-bit_in

3

Have you looked at the SQLite documentation? See SQLite Home Page

Also nothing stops you asking a question at: https://sqlite.org/forum/

Thats for your first/second questions.

Re the others, AFAIK the RowSet will be in memory once you’ve done the Select, and stays there until the RowSet variable goes out of scope or is set to Nil. And yes you can put it it a variable that doesn’t go out of scope.

4

Thank you, useful tip. Have added that in.

5

Did you try the cache?

For some 64-bit Applications, we use a 2 GB cache size, so the whole database may fit in the cache.
And the cache only grows as much as is needed, so a 2 GB cache and a 200 MB file may only get 200 MB in size.

6

Thanks. I’ve added the cache into my app, which is still under development so not possible to check on performance at the moment although I am sure that will help.

My questions are more about understanding, so as I develop in the future I know what is happening with the data to choose the right approach to get the best speed. Memory is not usually an issue for my apps and a 20mb database would be enormous, but the content will be constantly accessed and I need to have as much as possible held in memory as possible, but I also need to ensure that sets of changes are recorded safely to disk with commits. Scrambled data will not easily be able to be rebuilt, although I have a log stream that can be used to rebuild if necessary.

7

How do you achieve an in-memory DB? By using the sqlite pragma command? Or is that your question?

Yes, you’d open a sqlite3 file, and then use a pragma to set it to in-memory. That means that the in-memory copy is still tied to the file, and sqlite may still write back data to the file when it feels it’s necessary. And during this, the data in the file may be inconstent until you properly close the DB.

Therefore please heed my warning: If your app should crash while working with an in-memory DB, it’s not unlikely that you’ll end up with a corrupted on-disk database. I had that happen multiple times in my iClip app, to the point where customers lost a lot of data. I have since decided never to use in-memory DBs for critical data that I want to persist on disk. If you still want to do this, I suggest you first create a copy of the DB file, then open that with the in-memory setting, then, only when finished, close the DB and replace the file with the previous one, so that you can make sure that you never end up with a corrupted database file on disk.