Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-4971

Query runs 10 times more slowly with optimizer turned ON

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.7.1.1
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Normal
    • Bug behavior facts:
      Performance, Seen in production

      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

        Attachments

        1. optimizer-off.sql
          3 kB
          Graham Charters
        2. optimizer-on.sql
          3 kB
          Graham Charters
        3. rita.derby.zip
          3.92 MB
          Graham Charters

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              gcc Graham Charters
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: