Is it possible to limit the amount of memory SQLite uses while processing an aggregate query?
I have a 1GB database containing a single table. Simple queries against this table (SELECT COUNT(*), etc.) run without using more than a few MBs of memory; the amount used seems to correspond directly with the size of the page cache, as I expected. If I execute an aggregate query that contains a GROUP BY clause, however, the memory usage seems to jump quite a bit - the memory usage seems to correlate with the number of columns in the GROUP BY. Grouping by three columns using a couple hundred megs of memory; grouping by eight columns uses more than 1.3GB!. Given that the queries I'm testing with will return a lot of rows, I'm guessing that the memory is being used to store the result data before it's returned to the caller (or, if the query is part of a CREATE TABLE AS or INSERT INTO ... SELECT statement, before being inserted into the destination table). My other theory is that the memory is being used to hold intermediate results while performing the grouping. Regardless of the cause, is this avoidable? Given its nature as an embedded database, this just doesn't seem right to me, so I'm hoping so. Thanks -Tom
