Well, I'm using the command line tool that comes with SQLite and there is no ORDER BY clause in my query, so both the good news and the bad news is that it certainly seems like something that SQLite is doing, uhh... sub-optimally, shall we say. :)
I'm working my way through the VDBE, attempting to figure out what's going on, and there's one thing to which I can't yet find an answer, and it's driving me crazy: when handling the AggFocus operation, AggInsert is called if no entry is found in the b-tree for a given agg key. AggInsert allocates the memory necessary for the new aggregate bucket, adds it to the b-tree, and makes it the current bucket. All well and good. But where does that bucket get freed? Doesn't the next call to AggInsert clobber the pointer to the previously-allocated bucket? Unless I'm misunderstanding, sqlite3BtreeInsert makes a copy of the data that needs to be stored (which only makes sense, if the b-tree is being written to disk...), so I don't think that sqlite3BtreeInsert can be responsible for it, right? Thanks -Tom > -----Original Message----- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 24, 2005 2:21 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Memory usage for queries containing a > GROUP BY clause > > On Thu, 2005-03-24 at 13:59 -0500, Thomas Briggs wrote: > > I feel like I'm missing something, but that didn't seem > to help. I > > can see in the code why it should be behaving differently > (many thanks > > for the hint on where to look, BTW), but the memory usage > is unchanged. > > > > I modified sqliteInt.h to define SQLITE_OMIT_MEMORYDB, > then verified > > that it is defined by: > > a) inserting garbage into vdbeaux.c to prevent it from > compiling when > > SQLITE_OMIT_MEMORYDB is defined > > b) tried to attach to database :memory: without success > (it created a > > file name :memory: instead), and > > c) started the command line tool without specifying a > database and > > noting that the main database is a file named $CD/:memory: > > > > Am I missing something stupid here? Or is all my memory > being used > > somewhere other than constructing the b-tree used for aggregation? > > > > It might be the case that SQLite is using memory in ways that > are extravagent, wasteful, and unnecessary. Or you could be doing > something wrong in your app. Hard to say. > > Another big users of memory is ORDER BY. If the ORDER BY clause > cannot be satisfied by use of an index, then the entire result > set is pulled into memory and sorted there. Unlike the aggregate > issue, there is no easy fix for getting ORDER BY to work off of > disk, except appropriate use of indices in your schema. > -- > D. Richard Hipp <[EMAIL PROTECTED]> > >
