db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tony Brusseau (JIRA)" <j...@apache.org>
Subject [jira] Issue Comment Edited: (DERBY-4422) Extremely slow subqueries when subquerying on strings
Date Thu, 12 Nov 2009 16:53:39 GMT

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

Tony Brusseau edited comment on DERBY-4422 at 11/12/09 4:53 PM:
----------------------------------------------------------------

Knut,

Thanks for looking into this. The original query that I posted is the most simplified query
I could come up with that exposes the issue. Unfortunately, the real query I'm trying to optimize
can't be simplified in the manner you described and remain logically equivalent (both the
inner query and outter query make noncorrelated references to CONCEPT_STRINGS)..

Here is the query I really want to work:

SELECT DISTINCT C.EXTERNAL_ID, C.OWL_NL_ID, CYCL, C.PREFERRED_NL,
   C.PREFERRED_NL_SORTABLE, C.COMMENT, C.CONCEPT_TYPE, CS.NL, CS.STRING_TYPE,
   C.INTERNAL_ID FROM OC_CONCEPTS C, OC_CONCEPT_STRINGS CS
WHERE
   (C.INTERNAL_ID IN (SELECT DISTINCT OCS.CONCEPT_ID FROM OC_CONCEPT_STRINGS OCS WHERE OCS.NL_LC
like 'dash%'))
   AND
   (C.INTERNAL_ID = CS.CONCEPT_ID)
ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE 


      was (Author: apb):
    Knut,

Thanks for looking into this. The original query that I posted is the most simplified query
I could come up with that exposes the issue. Unfortunately, the real query I'm trying to optimize
can't be simplified in the manner you described and remain logically equivalent (both the
inner query and outter query make uncorrelated references to CONCEPT_STRINGS)..

Here is the query I really want to work:

SELECT DISTINCT C.EXTERNAL_ID, C.OWL_NL_ID, CYCL, C.PREFERRED_NL,
   C.PREFERRED_NL_SORTABLE, C.COMMENT, C.CONCEPT_TYPE, CS.NL, CS.STRING_TYPE,
   C.INTERNAL_ID FROM OC_CONCEPTS C, OC_CONCEPT_STRINGS CS
WHERE
   (C.INTERNAL_ID IN (SELECT DISTINCT OCS.CONCEPT_ID FROM OC_CONCEPT_STRINGS OCS WHERE OCS.NL_LC
like 'dash%'))
   AND
   (C.INTERNAL_ID = CS.CONCEPT_ID)
ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE 

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


Mime
View raw message