Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <18962895.1186013272932.JavaMail.jira@brutus>
Date: Wed, 1 Aug 2007 17:07:52 -0700 (PDT)
From: "James F. Adams (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Created: (DERBY-2986) Query involving CASE statement
 significantly slower in 10.3.1.4 than in 10.2.2.0
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

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
    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.