[DERBY-4971] Query runs 10 times more slowly with optimizer turned ON

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.7.1.1
• Component/s: SQL
• Labels: derby_triage10_8
• Urgency: Normal
• Bug behavior facts: Performance, Seen in production
• Assignee: Unassigned
• Reporter: Graham Charters
• Created: 13/Jan/11 17:14
• Updated: 16/Feb/11 01:24

Description

Derby takes 10 times as long to execute the attached query on the attached database when the optimizer is allowed to choose its own join order.

chris@fen-desktop2(tmp)$ cat ij
#!/bin/sh

DERBY_DIR=/home/chris/.m2/repository/org/apache/derby
DERBY_VER=10.7.1.1
DERBY_JAR=$DERBY_DIR/derby/$DERBY_VER/derby-$DERBY_VER.jar
TOOLS_JAR=$DERBY_DIR/derbytools/$DERBY_VER/derbytools-$DERBY_VER.jar

java -cp $DERBY_JAR:$TOOLS_JAR $OPTIONS org.apache.derby.tools.ij "$@"

chris@fen-desktop2(tmp)$ time ./ij optimizer-on.sql
ij version 10.7
ij> connect 'jdbc:derby:;databaseName=/tmp/rita.derby;user=rita';
ij> SELECT journey.transport_company AS journey_transport_provider, ...
1 row selected
ij> quit;

real 0m47.459s
user 0m47.067s
sys 0m0.344s

chris@fen-desktop2(tmp)$ time ./ij optimizer-off.sql
...
real 0m3.913s
user 0m4.100s
sys 0m0.148s