Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <306257780.1257872128225.JavaMail.jira@brutus>
Date: Tue, 10 Nov 2009 16:55:28 +0000 (UTC)
From: "Knut Anders Hatlen (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-4422) Extremely slow subqueries when
 subquerying on strings
In-Reply-To: <1043357842.1256319539373.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ https://issues.apache.org/jira/browse/DERBY-4422?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12775939#action_12775939 ] 

Knut Anders Hatlen commented on DERBY-4422:
-------------------------------------------

Hi Bryan,

The condition you quoted was for flattening into a normal join. There is also an option to flatten into an exists join (http://db.apache.org/derby/docs/10.5/tuning/ctuntransform25868.html) which does not have that condition and should apply if the outer DISTINCT does not provide that guarantee.

I'm not sure, though, if the guarantee provided by the outer DISTINCT is what the manual talks about, or if it actually requires that the sub-query does not return duplicates, even with the inner DISTINCT removed.

> Extremely slow subqueries when subquerying on strings
> -----------------------------------------------------
>
>                 Key: DERBY-4422
>                 URL: https://issues.apache.org/jira/browse/DERBY-4422
>             Project: Derby
>          Issue Type: Bug
>    Affects Versions: 10.5.3.0
>         Environment: Suse Unix 10.3.
>            Reporter: Tony Brusseau
>         Attachments: badqp.txt, derby.log, fixed-time-derby.log, goodqp.txt, SQLFile1.sqlaa.gz, SQLFile1.sqlab.gz, SQLFile2.sqlaa.gz, SQLFile2.sqlab.gz, SQLFile2.sqlac.gz, tmp2.sql, unlimited-time-derby.log
>
>
> SELECT DISTINCT C.INTERNAL_ID 
> FROM OC_CONCEPTS C
> WHERE
>    (C.INTERNAL_ID IN (SELECT DISTINCT OCS.CONCEPT_ID FROM OC_CONCEPT_STRINGS OCS WHERE OCS.CONCEPT_ID=1))
> Both queries above are trivial and they are both acting on fully indexed primary key columns. This query runs just fine. 
> However, when I make the small modification of searching on an indexed text column in the sub query, it takes over 6 seconds to run, even though both queries independently take < 1ms run.
> SELECT DISTINCT C.INTERNAL_ID 
> FROM OC_CONCEPTS C
> WHERE
>    (C.INTERNAL_ID IN (SELECT DISTINCT OCS.CONCEPT_ID FROM OC_CONCEPT_STRINGS OCS WHERE OCS.NL_LC = 'dash'))

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