- I'm working on a project that maps a large set of data across geographic locations and time (this is a personal project so I have complete control in terms of optimizations)
- Writes are very infrequent, and most of my optimizations are tailored towards reads
- I picked sqlite due to its small footprint, ease of setup, and good performance when concurrent writes are irrelevant
- The only indexes I've needed to rely on so far was for geographic regions (the time data is not published as frequently and I typically just create a new table whenever more data is published - such that each table is effectively a snapshot of data in time)
- For the most part this worked well, but some queries were very slow, mainly due to the fact that I needed to fetch data from 30+ tables and then run computations on each one
- To work around this, I created a "cache" table, that pre-computes these computations and stores them in a flattened table where rows correspond to geographic regions and columns to the times/snapshots when the readings were taken
- This works much better than the approach without the cache table, but still not good enough, and I'm noticing what seems like a linear increase in time to perform the query as the number of columns increases: doubling the number of returned columns (time snapshots) doubles the execution time of the query. The query behaves as if the actual scan/search is free and most of the time is spent grabbing column data. Maybe I'm being naive, but I would assume that grabbing 10 columns from a row would not take 10 times as much time as grabbing 1 column from the same row. I understand that I/O operations are the most operationally expensive part of the query, but I would assume since the columns are stored next to each other on disk, the database could use something similar to
memcpyto load all of the columns at once rather than loading them individually?
I'm not opposed to using a different mechanism for storing the cache data, as long as it doesn't add unnecessary complexity to my project, maybe sqlite is a wrong tool for the job here (I'd consider Memcached, but something like Redis may be overkill for me), or maybe I just need to organize my data differently (i.e. use rows to represent time as well and have region + time be the compound primary key)?
- Does it seem reasonable that query time increases linearly with number of columns selected?
- Can I do anything differently to mitigate this (i.e. structure data differently in SQLITE or use a different solution)?