You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by John English <jo...@gmail.com> on 2011/09/05 13:02:22 UTC
Selecting max/min
I have a table of results for several tests of several items where each
of the results is measured at different times, and I want to be able to
get the complete rows corresponding to the largest, smallest and most
recent result for each test on each item. I can for example get the
largest result for each test on each item like so:
SELECT item,test,MAX(result) AS maxr FROM results GROUP BY item,test;
but if I then want the whole row for each test on a single item, I end
up doing something like this:
CREATE VIEW max_view AS
SELECT item,test,MAX(result) AS maxr
FROM results GROUP BY item,test;
SELECT * FROM results,max_view
WHERE results.item=?
AND results.item=max_view.item AND result=maxr;
So with three views (max, min and latest) I can select from the results
table and the three views to pull out the rows I need. This looks as if
it will be horribly inefficient (particularly for big tables), what with
selecting stuff from the same table four times and then throwing most of
it away.
Can anyone suggest a more elegant solution?
TIA,
------------------------------------------------------------------------
John English | My old University of Brighton home page is still here:
| http://www.cem.brighton.ac.uk/staff/je/
------------------------------------------------------------------------
Re: Selecting max/min
Posted by John English <jo...@gmail.com>.
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
values?
> 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/
------------------------------------------------------------------------
RE: Selecting max/min
Posted by Juul Vanparijs <jv...@cressidatechnology.com>.
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.
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)!!!!
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...
Hope this helps,
Juul Vanparijs
Senior Developer
Cressida Technology Ltd
-----Original Message-----
From: John English [mailto:john.foreign@gmail.com]
Sent: Monday, September 05, 2011 1:02 PM
To: Derby Discussion
Subject: Selecting max/min
I have a table of results for several tests of several items where each of
the results is measured at different times, and I want to be able to get the
complete rows corresponding to the largest, smallest and most recent result
for each test on each item. I can for example get the largest result for
each test on each item like so:
SELECT item,test,MAX(result) AS maxr FROM results GROUP BY item,test;
but if I then want the whole row for each test on a single item, I end up
doing something like this:
CREATE VIEW max_view AS
SELECT item,test,MAX(result) AS maxr
FROM results GROUP BY item,test;
SELECT * FROM results,max_view
WHERE results.item=?
AND results.item=max_view.item AND result=maxr;
So with three views (max, min and latest) I can select from the results
table and the three views to pull out the rows I need. This looks as if it
will be horribly inefficient (particularly for big tables), what with
selecting stuff from the same table four times and then throwing most of it
away.
Can anyone suggest a more elegant solution?
TIA,
------------------------------------------------------------------------
John English | My old University of Brighton home page is still here:
| http://www.cem.brighton.ac.uk/staff/je/
------------------------------------------------------------------------
-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1392 / Virus Database: 1520/3877 - Release Date: 09/04/11