Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: domain of ronchalant@gmail.com designates
 209.85.221.190 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=content-type:mime-version:subject:from:in-reply-to:date
         :content-transfer-encoding:message-id:references:to:x-mailer;
        b=V2iSBj5z9OZ58KjrJ6j1oNS6smHYcMZ8pDvArkGrXjz/wR7FDVFAOZyGOx4Vidmnxn
         3/eeEdkpWzGsKhHltNVhYzWRHVSlUdfUEckwra/TSQraJrVfvTPXrqmKIGbyv4SJsnpv
         fmlakSCVXa+lsncLPbrM8bK2eWndl5l6gjqlk=
Content-Type: text/plain; charset=windows-1252
Mime-Version: 1.0 (Apple Message framework v1077)
Subject: Re: OutOfMemoryErrors on group by select
From: Ronald Rudy <ronchalant@gmail.com>
In-Reply-To: <4d2670741002080637u4483e31scd1065ce32c78904@mail.gmail.com>
Date: Mon, 8 Feb 2010 09:46:36 -0500
Content-Transfer-Encoding: quoted-printable
Message-Id: <623833E5-84DC-4FFF-AED2-91978097EB4E@gmail.com>
References: <E337D445-5890-465F-A8AB-BBC05472D468@gmail.com>
 <4d2670741002080621o3409f084t288587d3088738aa@mail.gmail.com>
 <F269E3CF-8B05-40EB-86A6-C78122BD8DCE@gmail.com>
 <4d2670741002080637u4483e31scd1065ce32c78904@mail.gmail.com>
To: "Derby Discussion" <derby-user@db.apache.org>

On its own the query seems to work ok, it seems that under load it =
pushes it over the line.  My memory usage goes to ~100MB right away just =
executing the query.  Repeated executions of the query don't seem to =
move the needle much though. =20

It's not really tenable right now to move to 10.5, though I'd like to =
during our next major revision (in the next month) so I can take =
advantage of the OFFSET capabilities. =20

I guess I'm more looking for general guidelines for managing memory =
usage in Derby - are there page size settings or anything else I can =
pass into Derby to force it to utilize less memory at the expense of =
performance?  I set the pageCacheSize at 2000, are there other params =
that will help limit memory usage in Derby?

Also, it seems in general that aggregate functionality (for example =
sum() functions on group by's) is rather poor performing in Derby - is =
there any way to improve this outside of indexing?  When I did a =
benchmark where in one implementation I straight iterated over the =
results and aggregated my grouped results in simple in-memory Map =
objects as compared against executing a Derby group by directly, the =
results were identical - is there something I can do here?


On Feb 8, 2010, at 9:37:59 AM, Peter Ondru=9Aka wrote:

> Try creating a non-unique index on the column you group by. Have you
> tried more recent Derby version if you can reproduce that there?
>=20