db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Juul Vanparijs" <jvanpar...@cressidatechnology.com>
Subject RE: Selecting max/min
Date Mon, 05 Sep 2011 18:59:52 GMT
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


Mime
View raw message