db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tomi N/A" <hef...@gmail.com>
Subject Re: performance, memory consumption...and a big hello :)
Date Mon, 10 Aug 2009 20:51:56 GMT
2009/7/29 Rick Hillegas <Richard.Hillegas@sun.com>:

> Some great work has been put into improving Derby's "explain" capabilities
> in the next feature release. In the meantime, you can view Derby query plans
> by using the SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS() system procedure.
> Please see the Derby Tuning Guide:
> http://db.apache.org/derby/docs/10.5/tuning/tuning-single.html#ctundepth13055

As I've already mentioned to Kristian, from what I understood from the
Tuning Guide, the information is all available once the query is
finished which was quite cumbersome in my case. I was interested in
testing a dozen or two combinations with different indexes setups and
if each test took a long time (because the query had to be actually
executed, rather than just it's plan displayed), it would take
forever.
I'm glad to hear there are improvements on the way as Derby remains
the DB of choice on my project.


> Have you tried
>
> ( id_group1, id )
>
> This index covers your query and is in a useful sort order. If Derby does
> not use this index on your large data set, then there may be a bug. It would
> be interesting to see the query plan which Derby follows when you have
> declared this index.

Interesting idea, Rick. At the moment I don't have the Derby instance
available, but I did try it out on postgresql. However, I've had no
lock: the column order doesn't seem to impact the execution plan: it's
invariably a seq. scan...still a nice try, though. I like your
methodical, precise analytical approach. :)

Mime
View raw message