db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <john.fore...@gmail.com>
Subject Re: Selecting max/min
Date Wed, 07 Sep 2011 09:04:11 GMT
On 05/09/2011 21:59, Juul Vanparijs wrote:
> Hi,
>  From experience I would not recommend using views (or nested joins) to
> achieve the desired results. Most RDBMS's, and Derby even a bit more, suffer
> from quite severe performance degradations to materialize (or whatever the
> correct term might be) the views... and these problems become even a lot
> worse when you try to use the views in joins.

This was my gut feeling.

> We develop several products that use (embedded) Derby, sometimes to store
> huge amounts of data. As the nature of our product requires very complex
> queries we started off using views and writing nifty SELECTs, but
> performance was quite appalling!
> Using Derby's explain and trace features we discovered that the relative
> costs of most of these statements was in the 100k's, if not millions range,
> while we expected them to be only in the order of some 10's...
> Changing our code to just basic SELECT statements, and issue SELECTs on each
> element of the result set internally resulted in PERFORMANCE IMPROVEMENTS OF
> OVER 90% (E.g. from +/- 1 minute to 4-5 secs)!!!!

Uh huh. So, just grab the results and loop through looking for the ones
I want, then? In this case, grab all the results relating to a single
particular item/test combo and process them all looking for the max/min

> At the time I ran into some discussions, also on the Derby forum, talking
> about these performance issues, but I unfortunately didn't save the links...

I'll have a look myself.

> Hope this helps,

Yes, many thanks for the advice!

  John English | My old University of Brighton home page is still here:
               | http://www.cem.brighton.ac.uk/staff/je/

View raw message