-1

Backstory:

  • 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 memcpy to 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)?

TLDR:

  • 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)?
2
  • 1
    Please provide the table and index definitions, some sample data, and some of the queries for which you noticed are starting to slow down. FWIW, columns or not, selecting 10x the amount of data will roughly take 10x as long to query. But I'm sure that's not exactly you're scenario / the exact issue you're experiencing.
    – J.D.
    Commented Oct 12, 2022 at 22:13
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Commented Oct 15, 2022 at 0:17

1 Answer 1

0

Time to retrieve data from any SQL database is obviously related to how much data is retrieved. Normally, a large part of the time is spent by the SQL engine to extract a little percentage of the data, selected through a complex query. When you need to select 10 rows in a million, a large part of time goes into searching for those 10 records, so when you find them the number of columns you read adds a negligible time to the total query.

But this is only true if the selection of the rows can be made using an index on a subset of the columns and not by reading the whole table.

Rows are stored on disk in pages (default page size is 4096 bytes). If your rows are 40 bytes long, sqlite can store 1000 of them in a single disk page. If your rows are 800 bytes long, sqlite can store only 50 of them on the same page. If your row size is more than 4000 bytes, every row will have to be split in two or more pages, requiring multiple disk reads for every row.

Anyway, every time sqlite needs to do a table scan it must read from disk a number of pages directly proportional to row size.

To mitigate the problem you should be sure to use an index to satisfy the WHERE clause of your query, so sqlite will have to fetch from the actual table only the rows you need. Of course, the time to fetch these particular rows will still be proportional to row size.

Also, this is a general principle that is valid for any SQL database, not only Sqlite.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.