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:[email protected]] 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
