db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2986) Query involving CASE statement significantly slower in 10.3.1.4 than in 10.2.2.0
Date Wed, 15 Aug 2007 15:37:34 GMT

    [ https://issues.apache.org/jira/browse/DERBY-2986?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12520008
] 

A B commented on DERBY-2986:
----------------------------

Thank you for running the regression tests, James.  Since everything ran cleanly I committed
the patch to trunk with svn # 566217:

  URL: http://svn.apache.org/viewvc?view=rev&rev=566217

The merge command for porting back to 10.3 executed cleanly:

  svn merge -r 566216:566217 https://svn.apache.org/repos/asf/db/derby/code/trunk

so it should hopefully be straightforward enough.  If no problems show up in the nightlies
from the commit to trunk, and if 10.3 tests run cleanly with the changes, then I'll commit
there, as well.

Note that I didn't include any new tests with my changes as the regression is performance
only (query plan and results stay the same), and thus I don't think there's a good way to
test for it.  (A variation of 60 seconds is probably not going to be noticed in the scope
of the full regression suites).  If anyone disagrees with this I can add the repro shown above
to lang/CaseExpressionTest.java with a follow-on commit.

> Query involving CASE statement significantly slower in 10.3.1.4 than in 10.2.2.0
> --------------------------------------------------------------------------------
>
>                 Key: DERBY-2986
>                 URL: https://issues.apache.org/jira/browse/DERBY-2986
>             Project: Derby
>          Issue Type: Bug
>          Components: Performance
>    Affects Versions: 10.3.1.4
>         Environment: Windows XP
>            Reporter: James F. Adams
>         Attachments: d2986_notTested_v1.patch
>
>
> A select of a CASE statement that performed acceptably in 10.2.2.0 is very slow in 10.3.1.4
the first time it is executed.
> The following example ij script:
> ELAPSEDTIME ON;
> CREATE table test1(id integer);
> CREATE table test2(id varchar(10));
> SELECT CASE  WHEN t.id = 1 THEN 'a'
>              WHEN t.id = 2 THEN 'b'
>              WHEN t.id = 3 THEN 'c'
>              WHEN t.id = 4 THEN 'd'
>              WHEN t.id = 5 THEN 'e'
>              WHEN t.id = 6 THEN 'f'
>              WHEN t.id = 7 THEN 'g'
>              WHEN t.id = 8 THEN 'h'
>              WHEN t.id = 11 THEN 'i'
>              WHEN t.id = 12 THEN 'j' 
>              WHEN t.id = 15 THEN 'k'
>              WHEN t.id = 16 THEN 'l'
>              WHEN t.id = 23 THEN 'm' 
>              WHEN t.id = 24 THEN 'n'
>              WHEN t.id = 27 THEN 'o'
>              WHEN t.id = 31 THEN 'p'
>              WHEN t.id = 41 THEN 'q'
>              WHEN t.id = 42 THEN 'r'
>              WHEN t.id = 50 THEN 's'
>              ELSE (SELECT t2.id
>                      FROM test2 t2
>                   )
>        END
> FROM test1 t;
> When run on 10.2.2.0 the select results in ELAPSED TIME = 187 milliseconds.
> When run on 10.3.1.4 the select results in ELAPSED TIME = 62281 milliseconds.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message