[
https://issues.apache.org/jira/browse/DERBY-3498?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Dag H. Wanvik updated DERBY-3498:
---------------------------------
Derby Categories: [Performance]
> Performance sensitive to FROM order list in three way join with GROUP BY
> ------------------------------------------------------------------------
>
> Key: DERBY-3498
> URL: https://issues.apache.org/jira/browse/DERBY-3498
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Reporter: Daniel John Debrunner
> Priority: Minor
>
> The Order Entry system test has this query in Checks.testCondition2():
> SELECT D.D_ID, D.D_W_ID,D.D_NEXT_O_ID,
> MAX(O.O_ID),MAX(N.NO_O_ID) FROM NEWORDERS N,
> DISTRICT D, ORDERS O WHERE D.D_W_ID=O.O_W_ID
> AND D.D_W_ID = N.NO_W_ID AND D.D_ID = O.O_D_ID
> AND D.D_ID = N.NO_D_ID GROUP BY
> D.D_ID,D.D_W_ID,D.D_NEXT_O_ID
> Changing the FROM list to put DISTRICT first reduced the running time from
> ~138seconds to ~105 and a similar reduction on another machine.
> (with a 1 warehouse database)
> Beyond that, the query seems to take too long. 10 rows will be returned and
> for each district row (returned row) the MAX() values in the select list
> should just be a max lookup in the primary key for the ORDERS and NEWORDERS
> table. The primary key for these tables is W_ID, D_ID, O_ID.
> Thus it should be a scan of the 10 row DISTRICT table with 20 index lookups,
> hard to see how that should table 100 seconds.
> One can see this by using the java-system-mini-codeline-jars target.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
