SQLite Caching and Application Caching
So, I am writing an application that uses SQLite very heavily. I am working on writing my application in a memory caching system that will allow me to sort and filter my data (essentially my own master data). I do this because it seems to me that this is a better / faster option than constantly making read requests from the SQLite database. Also, most of the fields / columns will be searchable / sortable, and the indexes on each one seem less than ideal. But I'm not sure. I know the SQLite database is cached in memory, but I don't know to what extent or how beneficial this is to me. Implementing my own caching system would be tricky and would probably add a footprint to my memory, especially since I load each table entirely into memory to do the sort / filters.I'm more than willing to do this if it helps the performance of my application, but will it? Is SQLite caching enough for me to rely solely on it or will it get bogged down when tables start getting large (10,000+ rows)? I'm probably asking if anyone has enough experience with SQLite to recommend one by one.
Before anyone asks: no, I can't use Core Data. Core Data is not flexible enough to use in my application.
source to share
Ok, here's what I get it: the choice depends a lot on your requirements. I ended up deleting (as far as possible) the SQLite cache, loading what I needed, and sorting / filtering with my own routines. This is great for me. But I figured out by doing this that this would not work in many situations. In particular, I've done a lot to make sure my DB size is as small as possible. I mostly only store plain / small text and numbers. Everything else is a link to an external file. This makes my database small enough to be used less as a database and more as an indexing service that works well for loading information into memory and sorting / filtering.
So the answer depends on the database. If you are storing large fields that could potentially take up a lot of memory, it is probably best to let SQLite handle the cache. On the other hand, if you know the fields will be small, the SQLite cache will only bloat your memory, and round trips to the database to sort / filter the data will increase your latency. It's better to do sorting / filtering instead, although I admit that's a lot of work. But in the end it made my application much faster than round-trip it to the DB.
source to share