You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "James F. Adams (JIRA)" <ji...@apache.org> on 2007/08/03 02:51:52 UTC
[jira] Commented: (DERBY-2986) Query involving CASE statement
significantly slower in 10.3.1.4 than in 10.2.2.0
[ https://issues.apache.org/jira/browse/DERBY-2986?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12517416 ]
James F. Adams commented on DERBY-2986:
---------------------------------------
The following simplified version of the script:
ELAPSEDTIME ON;
values CASE WHEN 10 = 1 THEN 'a'
WHEN 10 = 2 THEN 'b'
WHEN 10 = 3 THEN 'c'
WHEN 10 = 4 THEN 'd'
WHEN 10 = 5 THEN 'e'
WHEN 10 = 6 THEN 'f'
WHEN 10 = 7 THEN 'g'
WHEN 10 = 8 THEN 'h'
WHEN 10 = 11 THEN 'i'
WHEN 10 = 12 THEN 'j'
WHEN 10 = 15 THEN 'k'
WHEN 10 = 16 THEN 'l'
WHEN 10 = 23 THEN 'm'
WHEN 10 = 24 THEN 'n'
WHEN 10 = 27 THEN 'o'
WHEN 10 = 31 THEN 'p'
WHEN 10 = 41 THEN 'q'
WHEN 10 = 42 THEN 'r'
WHEN 10 = 50 THEN 's'
ELSE '*'
END;
results in:
10.2.2.0 ELAPSED TIME = 297 milliseconds
10.3.1.4 ELAPSED TIME = 52000 milliseconds
> 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
>
> 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.