Hi,
This is not a bug report per se. It may be a suggestion for improvement or enhancement, depending on how common this kind of occurrence may be.
The problem is as follows. I have a database in which I have a bunch of measures. I have written some views that summarize these measures (statistics such as count, min, max, percentile values, etc.). There are 13 measures, and 111 statistics for each measure.
When I try to run a query that produces all these statistics in a single output (111 rows x 13 columns), I consistently run into "Out of Memory" errors on my 10-year old Windows 7 PC with 4 GB of RAM. The query does run on my work laptop with Windows 10 and 16 GB of RAM, so there is no error in the query itself.
The query itself is somewhat heavy. It uses 13 other views, and each of these views uses another view that uses window functions and aggregates heavily. It takes about 15 to 20 seconds to run even on my powerful work laptop.
The work-around to this on my memory-challenged laptop is to create a separate temp table for each of the 13 measures (13 tables total) and then do a join on all 13 to create the final output I want (111 rows x 13 columns output). This process of creating the 13 temp tables and then joining them takes about 30 seconds to a minute on my personal laptop. Once again, the individual queries work and they are quite quick, it is just the process of running all the views and joining them in memory that causes the out of memory error.
Given that the query can produce the results using this work-around, I just thought I would post here to see if it would be possible for SQLite to live up to its Lite moniker with an enhancement: when the system is running out of memory, write intermediate results to temp tables, and then try to use those temp tables internally to create the final output. All this can be done transparently without the user even knowing that this is happening. Now, this is coming from someone who has not looked at the SQLite source code even accidentally, so take that for what it is worth!
I have created a test database for you to play with if you are interested. The link is at https://drive.google.com/file/d/1oVsOxP6-9JrzN2GiedvcuqmQnRV0-wa-/view?usp=sharing .
If your computer is as underpowered as mine is, you will find it impossible to run the view "TripSummary". This is the view that is supposed to produce the 111 x 13 matrix as output. On such a computer, you will find that you can go to the table "UsefulQueries" and run the two queries you find there one after the other (the first one creates the 13 temp tables, and the second one joins them to produce the final matrix output).
Please let me know if something is not clear or you have questions. Please also feel free to let me know how to make my code more efficient and less memory-intensive in case I have done something unnecessary or stupid.
Thank you.