[jira] Commented: (DERBY-4422) Extremely slow subqueries when subquerying on strings

["Knut Anders Hatlen (JIRA)" <ji...@apache.org>]

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

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

I think both the queries above satisfy the requirements for being flattened to an exists join (http://db.apache.org/derby/docs/10.5/tuning/ctuntransform25868.html), which normally means that they should perform reasonably well.

If you could attach the output from dblook (at least the parts with table and index definitions for the tables involved in the queries) it will be easier for others to reproduce the problem.

Just to eliminate stale index cardinality statistics as the culprit, do you still see the problem if you first invoke the following two statements? (Replace 'APP' with the actual name of your schema.)

  CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'OC_CONCEPTS', NULL);
  CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'OC_CONCEPT_STRINGS', NULL);

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