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/
------------------------------------------------------------------------
