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
#else
db.SQLExecute "PRAGMA cache_size = 20000" // 20 MB
#endif

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.