The cache_size connection parameter specifies the amount of memory to allocate for the file cache. This cache is used to hold
recently used pages from the database file in memory so they can be accessed quickly when needed again, and also to collect
multiple modifications to a page before writing it back to storage. Accessing a page from the cache is many times faster than
reading from storage. Writing to storage is even more expensive, so grouping multiple modifications in a single write is important
for performance. Encrypted databases also benefit from the cache because decryption occurs only when the page is loaded into
the cache, and encryption occurs before the page is written back to storage. If the cache is sufficiently large, the overhead
of encryption becomes negligible.
As an example of cache usage, consider synchronization. While UltraLite is receiving a download, the rows are inserted into
the database, and referential integrity checks are performed. When inserted, the rows are also indexed - added to each index
on the table. So, while synchronizing, the cache will tend to hold the pages where the new rows are stored, as well as the
index pages for the current table. Synchronization performance will depend greatly on whether the cache is large enough to
contain an appropriate "working set" of pages for a table being synchronized. If the cache is too small, row inserts may require
repeated reads of index pages from storage, incurring a noticeable performance penalty over the case when the required index
pages fit in the cache.
If the cache size is not specified, or if you set the size to 0, the default size is used. If your testing shows the need
for better performance, you should increase the cache size. For Windows, the default cache is set to about 1.5% of total physical
memory, or 110% of the database file size, whichever is less. For Windows Mobile, the default cache size is limited to the
lesser of: 25% of total physical memory, 90% of available physical memory less 1MB, or 110% of the database file size.
By default, the size is in bytes. Use k, m, or g to specify units of kilobytes, megabytes, or gigabytes, respectively.
If you exceed the maximum cache size, it is automatically replaced with your platform's upper cache size limit. Increasing
the cache size beyond the size of the database does not provide any performance improvement, and a large cache size can interfere
with the number of other applications you can use.
Any leading or trailing spaces in parameter values are ignored. This connection parameter's value cannot include leading single
quotes, leading double quotes, or semicolons.