Xojo Conferences

« MBS FileMaker Plugin,… | Home | Arrived in Madrid for… »

Big SQLite Cache for 64-bit in Xojo

Whenever you open a SQLite database in your application, please remember to first send this command via SQL Execute:

PRAGMA cache_size = 20000

this will increase the cache. Default is 2000 pages and with a page size of 1024 bytes that's only 2 MB of cache. You can easily set it to 20000 pages on modern computers for 20 MB. This way you increase SQLite performance a lot!

Now the best cache for SQLite is one where the whole database fits in. On the other side if you set cache bigger, it will only grow until the whole database is in memory. So for one of our projects here, we now use 2 GB cache limit for SQLite:

#if Target64Bit then
  db.SQLExecute "PRAGMA cache_size = 2000000" // 2 GB
  db.SQLExecute "PRAGMA cache_size = 20000" // 20 MB

If the database is 10 MB, of course the cache will only be 10 MB. But if the database is 50 MB for a table and this table is queried, it helps a lot of the whole table fits into the cache. And with 64-bit memory is available and you do not risk running out of it soon, so using it for the database cache is a good idea.

This works for SQLiteDatabase, iOSSQLiteDatabase, SQLDatabaseMBS and SQLConnectionMBS in Xojo as well as with our MBS Filemaker Plugin when used with SQLite.
16 10 17 - 15:39
six comments

does this apply to single user sqlite only or does this include cubesql database server with sqlite file??
Trisha Duke - 02 11 17 - 10:43

For CubeSQL, please ask SQLabs.
I would expect they create a big cache on the server side this way.
Christian Schmitz (URL) - 02 11 17 - 10:47

on the cubesql admin , under setting, there is something called chuck size and on the side for the notes “maximum size in bytes for a cursor before splitter in chunks. higher values mean better performance but requires extra memory”

is this the same or similar to cache size?
Trisha Duke - 08 11 17 - 13:57

Well, I would expect that this is about how much data to be send in one chunk over network.
So this may not be related to memory cache for the database or prefetching.
Christian Schmitz (URL) - 08 11 17 - 14:45

i will check with sqlabs
Trisha Duke - 08 11 17 - 16:59

In your example you might want to use negative values for memory size rather than page count because the default page size is no longer 1024 bytes, it is 4096: https://sqlite.org/pragma.html#pragma_page_size
Aaron H - 07 03 19 - 15:22

Remember personal info?

Emoticons / Textile

Hide email:

Small print: All html tags except <b> and <i> will be removed from your comment. You can make links by just typing the url or mail-address.