db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jonas S Karlsson" <...@yesco.org>
Subject RE: How to optimize the query
Date Tue, 19 Oct 2004 18:53:47 GMT

David Zonsheine wrote:
> Where can I find the derby.properties?

You can set the property from the command line, when running ij for
example:

	java -Dderby.language.logQueryPlan=true org.apache.derby.tools.ij test.sql

For parsing the output, I posted a script the last time you talked
about the query plans. It's in the archive at:

http://nagoya.apache.org/eyebrowse/ReadMsg?listName=derby-dev@db.apache.org&msgNo=594

It may help in seing the "shape" of the query plan as it attempts to
extract that from the derby.log file.

In any case, if you have a query that optimizes badly/takes long time
on Derby, posting a similar query with testdata to reproduce the
issue, may be helpful.

Regarding hints, I cannot other than agree with Jeffrey Lichtman in:
http://nagoya.apache.org/eyebrowse/ReadMsg?listName=derby-dev@db.apache.org&msgNo=712
>Removing optimizer hints (and other hints) from the language because they 
>are non-standard is pretty ridiculous, in my opinion. Hints are almost 
>inevitably non-standard. The SQL standard doesn't even recognize the 
>concept of optimization, much less optimizer hints.

and Jeffrey Lichtman:
>I have been working with databases and optimizers for about twenty years. I
>have yet to see an optimizer that gets everything right every time.
>Optimizers rely on cost estimates and modeling that cannot always be
>accurate. Sometimes a poor query plan is caused by a bug, but sometimes
>it's caused by something the implementation didn't anticipate (or couldn't
>reasonably be expected to anticipate). Optimizer hints allow the user to do
>something in the inevitable cases where the query optimizer doesn't choose
>an adequate plan, for whatever reason. Without hints, the user is stuck
>until someone changes the optimizer - a task that is often very difficult.

This is very valid, and vital in my opinion.

/Jonas



Mime
View raw message