On Thu, 2005-03-24 at 10:09 -0500, Thomas Briggs wrote: > 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!. >
The entire results set of an aggregate query is held in memory. The only way to limit the memory usage of an aggregate query is to construct the query in such a way that the number of rows in the result set is limited. If you are using 1.3GB of memory to do an aggregate query, my guess is that there are a very large number of rows in the result set. It is, in theory, possible to reduce the memory requirements for an aggregate query if the GROUP BY clause specifies columns that are indexed. But SQLite does not currently implement that optimization. You can implement the optimization manually, though, by turning the GROUP BY clause into an ORDER BY clause, dropping the aggregate functions, then doing the aggregation manually as the rows come out of the database in order. -- D. Richard Hipp <[EMAIL PROTECTED]>
