[jira] Issue Comment Edited: (DERBY-4422) Extremely slow subqueries when subquerying on strings
[ https://issues.apache.org/jira/browse/DERBY-4422?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12865243#action_12865243 ] Tony Brusseau edited comment on DERBY-4422 at 5/7/10 2:06 PM: -- I've discovered another somewhat strange performance issue with a related query where having more than one string in an "IN" clause causes orders of magnitude slow down: 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, (SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71YxJScKRGxna3DeWNvcnAeK71Y0qCcKRGxna3DeWNvcnA' )) X WHERE (C.INTERNAL_ID = X.INTERNAL_ID) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 0.003 s, 0 error(s) occurred. 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, (SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71Y6VGcKRGxna3DeWNvcnAeK71Y7BKcKRGxna3DeWNvcnA' )) X WHERE (C.INTERNAL_ID = X.INTERNAL_ID) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 0.003 s, 0 error(s) occurred. 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, (SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71YxJScKRGxna3DeWNvcnAeK71Y0qCcKRGxna3DeWNvcnA', 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71Y6VGcKRGxna3DeWNvcnAeK71Y7BKcKRGxna3DeWNvcnA' )) X WHERE (C.INTERNAL_ID = X.INTERNAL_ID) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 2.37 s, 0 error(s) occurred. 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.EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71YxJScKRGxna3DeWNvcnAeK71Y0qCcKRGxna3DeWNvcnA', 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71Y6VGcKRGxna3DeWNvcnAeK71Y7BKcKRGxna3DeWNvcnA' )) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 0.009 s, 0 error(s) occurred. was (Author: apb): I've discovered another somewhat strange performance issue with a related query where having more than one string in an "IN" clause causes orders of magnitude slow down: 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, (SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71YxJScKRGxna3DeWNvcnAeK71Y0qCcKRGxna3DeWNvcnA' )) X WHERE (C.INTERNAL_ID = X.INTERNAL_ID) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 0.003 s, 0 error(s) occurred. 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, (SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71Y6VGcKRGxna3DeWNvcnAeK71Y7BKcKRGxna3DeWNvcnA' )) X WHERE (C.INTERNAL_ID = X.INTERNAL_ID) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 0.003 s, 0 error(s) occurred. 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, (SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71YxJScKRGxna3DeWNvcnAeK71Y0qCcKRGxna3DeWNvcnA', 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71Y6VGcKRGxna3DeWNvcnAeK71Y7BKcKRGxna3DeWNvcnA' )) X WHERE (C.INTERNAL_ID = X.INTERNAL_ID) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 2.37 s, 0
[jira] Issue Comment Edited: (DERBY-4422) Extremely slow subqueries when subquerying on strings
[ https://issues.apache.org/jira/browse/DERBY-4422?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12865243#action_12865243 ] Tony Brusseau edited comment on DERBY-4422 at 5/7/10 1:55 PM: -- I've discovered another somewhat strange performance issue with a related query where having more than one string in an "IN" clause causes orders of magnitude slow down: 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, (SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71YxJScKRGxna3DeWNvcnAeK71Y0qCcKRGxna3DeWNvcnA' )) X WHERE (C.INTERNAL_ID = X.INTERNAL_ID) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 0.003 s, 0 error(s) occurred. 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, (SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71Y6VGcKRGxna3DeWNvcnAeK71Y7BKcKRGxna3DeWNvcnA' )) X WHERE (C.INTERNAL_ID = X.INTERNAL_ID) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 0.003 s, 0 error(s) occurred. 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, (SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71YxJScKRGxna3DeWNvcnAeK71Y0qCcKRGxna3DeWNvcnA', 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71Y6VGcKRGxna3DeWNvcnAeK71Y7BKcKRGxna3DeWNvcnA' )) X WHERE (C.INTERNAL_ID = X.INTERNAL_ID) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 2.37 s, 0 error(s) occurred. 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.EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71YxJScKRGxna3DeWNvcnAeK71Y0qCcKRGxna3DeWNvcnA', 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71Y6VGcKRGxna3DeWNvcnAeK71Y7BKcKRGxna3DeWNvcnA' )) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 0.009 s, 0 error(s) occurred. was (Author: apb): I've discovered another somewhat strange performance issue with a related query where having more than one string in an "IN" clause causes orders of magnitude slow down: 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, (SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71YxJScKRGxna3DeWNvcnAeK71Y0qCcKRGxna3DeWNvcnA' )) X WHERE (C.INTERNAL_ID = X.INTERNAL_ID) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 0.003 s, 0 error(s) occurred. 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, (SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71Y6VGcKRGxna3DeWNvcnAeK71Y7BKcKRGxna3DeWNvcnA' )) X WHERE (C.INTERNAL_ID = X.INTERNAL_ID) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 0.003 s, 0 error(s) occurred. 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, (SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71YxJScKRGxna3DeWNvcnAeK71Y0qCcKRGxna3DeWNvcnA', 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71Y6VGcKRGxna3DeWNvcnAeK71Y7BKcKRGxna3DeWNvcnA' )) X WHERE (C.INTERNAL_ID = X.INTERNAL_ID) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 2.37 s, 0 e
[jira] Issue Comment Edited: (DERBY-4422) Extremely slow subqueries when subquerying on strings
[ https://issues.apache.org/jira/browse/DERBY-4422?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12865243#action_12865243 ] Tony Brusseau edited comment on DERBY-4422 at 5/7/10 1:32 PM: -- I've discovered another somewhat strange performance issue with a related query where having more than one string in an "IN" clause causes orders of magnitude slow down: 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, (SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71YxJScKRGxna3DeWNvcnAeK71Y0qCcKRGxna3DeWNvcnA' )) X WHERE (C.INTERNAL_ID = X.INTERNAL_ID) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 0.003 s, 0 error(s) occurred. 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, (SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71Y6VGcKRGxna3DeWNvcnAeK71Y7BKcKRGxna3DeWNvcnA' )) X WHERE (C.INTERNAL_ID = X.INTERNAL_ID) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 0.003 s, 0 error(s) occurred. 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, (SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71YxJScKRGxna3DeWNvcnAeK71Y0qCcKRGxna3DeWNvcnA', 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71Y6VGcKRGxna3DeWNvcnAeK71Y7BKcKRGxna3DeWNvcnA' )) X WHERE (C.INTERNAL_ID = X.INTERNAL_ID) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 2.37 s, 0 error(s) occurred. 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.EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71YxJScKRGxna3DeWNvcnAeK71Y0qCcKRGxna3DeWNvcnA', 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71Y6VGcKRGxna3DeWNvcnAeK71Y7BKcKRGxna3DeWNvcnA' )) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 0.009 s, 0 error(s) occurred. was (Author: apb): I've discovered another somewhat strange performance issue with a related query where having more than one string in an "IN" clause causes orders of magnitude slow down: 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, (SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71YxJScKRGxna3DeWNvcnAeK71Y0qCcKRGxna3DeWNvcnA' )) X WHERE (C.INTERNAL_ID = X.INTERNAL_ID) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 0.003 s, 0 error(s) occurred. 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, (SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71Y6VGcKRGxna3DeWNvcnAeK71Y7BKcKRGxna3DeWNvcnA' )) X WHERE (C.INTERNAL_ID = X.INTERNAL_ID) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 0.003 s, 0 error(s) occurred. 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, (SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71YxJScKRGxna3DeWNvcnAeK71Y0qCcKRGxna3DeWNvcnA', 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71Y6VGcKRGxna3DeWNvcnAeK71Y7BKcKRGxna3DeWNvcnA' )) X WHERE (C.INTERNAL_ID = X.INTERNAL_ID) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 2.37 s, 0 e
[jira] Issue Comment Edited: (DERBY-4422) Extremely slow subqueries when subquerying on strings
[ https://issues.apache.org/jira/browse/DERBY-4422?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12865243#action_12865243 ] Tony Brusseau edited comment on DERBY-4422 at 5/7/10 1:24 PM: -- I've discovered another somewhat strange performance issue with a related query where having more than one string in an "IN" clause causes orders of magnitude slow down: 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, (SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71YxJScKRGxna3DeWNvcnAeK71Y0qCcKRGxna3DeWNvcnA' )) X WHERE (C.INTERNAL_ID = X.INTERNAL_ID) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 0.003 s, 0 error(s) occurred. 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, (SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71Y6VGcKRGxna3DeWNvcnAeK71Y7BKcKRGxna3DeWNvcnA' )) X WHERE (C.INTERNAL_ID = X.INTERNAL_ID) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 0.003 s, 0 error(s) occurred. 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, (SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71YxJScKRGxna3DeWNvcnAeK71Y0qCcKRGxna3DeWNvcnA', 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71Y6VGcKRGxna3DeWNvcnAeK71Y7BKcKRGxna3DeWNvcnA' )) X WHERE (C.INTERNAL_ID = X.INTERNAL_ID) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 2.37 s, 0 error(s) occurred. was (Author: apb): I've discovered another somewhat strange performance issue with a related query where having more than one string in an "IN" clause causes orders of magnitude slow down): 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, (SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71YxJScKRGxna3DeWNvcnAeK71Y0qCcKRGxna3DeWNvcnA' )) X WHERE (C.INTERNAL_ID = X.INTERNAL_ID) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 0.003 s, 0 error(s) occurred. 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, (SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71Y6VGcKRGxna3DeWNvcnAeK71Y7BKcKRGxna3DeWNvcnA' )) X WHERE (C.INTERNAL_ID = X.INTERNAL_ID) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 0.003 s, 0 error(s) occurred. 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, (SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE EXTERNAL_ID IN ( 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71YxJScKRGxna3DeWNvcnAeK71Y0qCcKRGxna3DeWNvcnA', 'Mw2CHiu-uEr4nCkRsZ2tw3ljb3JwDYMeK71Y5HacKRGxna3DeWNvcnAeK71Y6VGcKRGxna3DeWNvcnAeK71Y7BKcKRGxna3DeWNvcnA' )) X WHERE (C.INTERNAL_ID = X.INTERNAL_ID) AND (C.INTERNAL_ID = CS.CONCEPT_ID) ORDER BY C.PREFERRED_NL_SORTABLE, C.INTERNAL_ID, CS.STRING_TYPE ==> Execution finished after 2.37 s, 0 error(s) occurred. > 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 >
[jira] Issue Comment Edited: (DERBY-4422) Extremely slow subqueries when subquerying on strings
[ https://issues.apache.org/jira/browse/DERBY-4422?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12841949#action_12841949 ] Tony Brusseau edited comment on DERBY-4422 at 3/5/10 7:43 PM: -- Per the documentation on materialization: http://db.apache.org/derby/docs/10.5/tuning/ctuntransform25857.html I rewrote the query from: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash')) and transformed it into: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS, (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash') X WHERE (OC_CONCEPTS.INTERNAL_ID = X.CONCEPT_ID) Now the query runs blindingly fast. I also made a similar change to the bigger query that I want to do, and got the same dramatic speedup. I guess the real bug here is that the optimizer is not automatically optimizing the queries in such a manner (or possibly that the materialization optimization is too narrow and should be broadened to apply to a larger subset of queries). There may be a secondary issue noted by Bryan where minor changes in the query yield a dramatically less good optimization strategy. was (Author: apb): Per the documentation on materialization: http://db.apache.org/derby/docs/10.5/tuning/ctuntransform25857.html I rewrote the query from: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash')) and transformed it into: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS, (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash') X WHERE (OC_CONCEPTS.INTERNAL_ID = X.CONCEPT_ID) Now the query runs blindingly fast. I also made a similar change to the bigger query that I want to do, and got the same dramatic speedup. I guess the real bug here is that the optimizer is not automatically optimizing the queries in such a manner (or possibly that the materialization optimization is too narrow and should be broadened). There may be a secondary issue noted by Bryan where minor changes in the query yield a dramatically less good optimization strategy. > 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.
[jira] Issue Comment Edited: (DERBY-4422) Extremely slow subqueries when subquerying on strings
[ https://issues.apache.org/jira/browse/DERBY-4422?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12841949#action_12841949 ] Tony Brusseau edited comment on DERBY-4422 at 3/5/10 7:42 PM: -- Per the documentation on materialization: http://db.apache.org/derby/docs/10.5/tuning/ctuntransform25857.html I rewrote the query from: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash')) and transformed it into: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS, (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash') X WHERE (OC_CONCEPTS.INTERNAL_ID = X.CONCEPT_ID) Now the query runs blindingly fast. I also made a similar change to the bigger query that I want to do, and got the same dramatic speedup. I guess the real bug here is that the optimizer is not automatically optimizing the queries in such a manner (or possibly that the materialization optimization is too narrow and should be broadened). There may be a secondary issue noted by Bryan where minor changes in the query yield a dramatically less good optimization strategy. was (Author: apb): Per the documentation on materialization: http://db.apache.org/derby/docs/10.5/tuning/ctuntransform25857.html I rewrote the query from: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash')) and transformed it into: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS, (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash') X WHERE (OC_CONCEPTS.INTERNAL_ID = X.CONCEPT_ID) Now the query runs blindingly fast. I also made a similar change to the bigger query that I want to do, and got the same dramatic speedup. I guess the real bug here is that the optimizer is not automatically optimizing the queries in such a manner. There may be a secondary issue noted by Bryan where minor changes in the query yield a dramatically less good optimization strategy. > 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.
[jira] Issue Comment Edited: (DERBY-4422) Extremely slow subqueries when subquerying on strings
[ https://issues.apache.org/jira/browse/DERBY-4422?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12841949#action_12841949 ] Tony Brusseau edited comment on DERBY-4422 at 3/5/10 6:12 PM: -- Per the documentation on materialization: http://db.apache.org/derby/docs/10.5/tuning/ctuntransform25857.html I rewrote the query from: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash')) and transformed it into: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS, (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash') X WHERE (OC_CONCEPTS.INTERNAL_ID = X.CONCEPT_ID) Now the query runs blindingly fast. I also made a similar change to the bigger query that I want to do, and got the same dramatic speedup. I guess the real bug here is that the optimizer is not automatically optimizing the queries in such a manner. There may be a secondary issue noted by Bryan where minor changes in the query yield a dramatically less good optimization strategy. was (Author: apb): Per the documentation on materialization: http://db.apache.org/derby/docs/10.5/tuning/ctuntransform25857.html I rewrote the query from: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash')) and transformed it into: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS, (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash') X WHERE (OC_CONCEPTS.INTERNAL_ID = X.CONCEPT_ID) Now the query runs blindingly fast. I also made a similar change to the bigger query that I want to do, and got the same dramatic speedup. I guess the real bug here is that the optimizer is not automatically optimizing the queries in such a manner. There may be a secondary issue noted by Bryan where minor changes in the query yields a dramatically less good optimization strategy. > 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.
[jira] Issue Comment Edited: (DERBY-4422) Extremely slow subqueries when subquerying on strings
[ https://issues.apache.org/jira/browse/DERBY-4422?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12841949#action_12841949 ] Tony Brusseau edited comment on DERBY-4422 at 3/5/10 6:11 PM: -- Per the documentation on materialization: http://db.apache.org/derby/docs/10.5/tuning/ctuntransform25857.html I rewrote the query from: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash')) and transformed it into: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS, (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash') X WHERE (OC_CONCEPTS.INTERNAL_ID = X.CONCEPT_ID) Now the query runs blindingly fast. I also made a similar change to the bigger query that I want to do, and got the same dramatic speedup. I guess the real bug here is that the optimizer is not automatically optimizing the queries in such a manner. There may be a secondary issue noted by Bryan where minor changes in the query yields a dramatically less good optimization strategy. was (Author: apb): Per the documentation on materialization: http://db.apache.org/derby/docs/10.5/tuning/ctuntransform25857.html I rewrote the query from: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash')) and transformed it into: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS, (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash') X WHERE (OC_CONCEPTS.INTERNAL_ID = X.CONCEPT_ID) Now the query runs blindingly fast. I also made a similar change to the bigger query that I want to do, and got the same dramatic speedup. I guess the real bug here is that the optimizer is not automatically optimizing the queries in such a manner. There may be a secondary issue noted by Bryan where minor changes in the query yields a dramatically different optimization strategy. > 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.
[jira] Issue Comment Edited: (DERBY-4422) Extremely slow subqueries when subquerying on strings
[ https://issues.apache.org/jira/browse/DERBY-4422?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12841949#action_12841949 ] Tony Brusseau edited comment on DERBY-4422 at 3/5/10 6:09 PM: -- Per the documentation on materialization: http://db.apache.org/derby/docs/10.5/tuning/ctuntransform25857.html I rewrote the query from: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash')) and transformed it into: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS, (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash') X WHERE (OC_CONCEPTS.INTERNAL_ID = X.CONCEPT_ID) Now the query runs blindingly fast. I also made a similar change to the bigger query that I want to do, and got the same dramatic speedup. I guess the real bug here is that the optimizer is not automatically optimizing the queries in such a manner. There may be a secondary issue noted by Bryan where minor changes in the query yields a dramatically different optimization strategy. was (Author: apb): Per the documentation on materialization: http://db.apache.org/derby/docs/10.5/tuning/ctuntransform25857.html I rewrote the query from: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash')) and transformed it into: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS, (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash') X WHERE (OC_CONCEPTS.INTERNAL_ID = X.CONCEPT_ID) Now the query runs blindingly fast. I also made a similar change to the bigger query that I want to do, and got the same dramatic speedup. I guess the real bug here is that the optimizer is not automatically optimizing the queries in such a manner. There may be a secondary issue noted by Bryan where minor changes in the query yields dramatically different optimization strategy. > 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.
[jira] Issue Comment Edited: (DERBY-4422) Extremely slow subqueries when subquerying on strings
[ https://issues.apache.org/jira/browse/DERBY-4422?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12841949#action_12841949 ] Tony Brusseau edited comment on DERBY-4422 at 3/5/10 5:54 PM: -- Per the documentation on materialization: http://db.apache.org/derby/docs/10.5/tuning/ctuntransform25857.html I rewrote the query from: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash')) and transformed it into: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS, (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash') X WHERE (OC_CONCEPTS.INTERNAL_ID = X.CONCEPT_ID) Now the query runs blindingly fast. I also made a similar change to the bigger query that I want to do, and got the same dramatic speedup. I guess the real bug here is that the optimizer is not automatically optimizing the queries in such a manner. There may be a secondary issue noted by Bryan where minor changes in the query yields dramatically different optimization strategy. was (Author: apb): Per the documentation on materialization: http://db.apache.org/derby/docs/10.5/tuning/ctuntransform25857.html I rewrote the query from: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash')) and transformed it into: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS, (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash') X WHERE (OC_CONCEPTS.INTERNAL_ID = X.CONCEPT_ID) Now the query runs blindingly fast. I also made a similar change to the bigger query that I want to do, and got the same dramatic speedup. I guess the real bug here is that the optimizer is not automatically optimizing the queries in such a manner. There may be a secondary issue noted by Bryan where minor changes in the query yields dramatically different optimizations. > 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.
[jira] Issue Comment Edited: (DERBY-4422) Extremely slow subqueries when subquerying on strings
[ https://issues.apache.org/jira/browse/DERBY-4422?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12841949#action_12841949 ] Tony Brusseau edited comment on DERBY-4422 at 3/5/10 5:45 PM: -- Per the documentation on materialization: http://db.apache.org/derby/docs/10.5/tuning/ctuntransform25857.html I rewrote the query from: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash')) and transformed it into: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS, (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash') X WHERE (OC_CONCEPTS.INTERNAL_ID = X.CONCEPT_ID) Now the query runs blindingly fast. I also made a similar change to the bigger query that I want to do, and got the same dramatic speedup. I guess the real bug here is that the optimizer is not automatically optimizing the queries in such a manner. There may be a secondary issue noted by Bryan where minor changes in the query yields dramatically different optimizations. was (Author: apb): Per the documentation on materialization: http://db.apache.org/derby/docs/10.5/tuning/ctuntransform25857.html I rewrote the query from: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash')) and transformed it into: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS, (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash') X WHERE (OC_CONCEPTS.INTERNAL_ID = X.CONCEPT_ID) Now the query runs blindingly fast. I also made a similar change to the bigger query that I want to do, and got the same dramatic speedup. I guess the real bug here is that the optimizer is not automatically optimizing the queries in such a manner > 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.
[jira] Issue Comment Edited: (DERBY-4422) Extremely slow subqueries when subquerying on strings
[ https://issues.apache.org/jira/browse/DERBY-4422?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12777079#action_12777079 ] Tony Brusseau edited comment on DERBY-4422 at 11/12/09 9:11 PM: The documentation on materialization seems like it might be relevant here: http://db.apache.org/derby/docs/10.5/tuning/ctuntransform25857.html Since the query is uncorrelated (and if it were unflattenable) then the query should be optimized like "expected". was (Author: apb): The documentation on materialization seems relevant here: http://db.apache.org/derby/docs/10.5/tuning/ctuntransform25857.html Since the query is uncorrelated (and if it were unflattenable) then the query should be optimized like expected. > 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.
[jira] Issue Comment Edited: (DERBY-4422) Extremely slow subqueries when subquerying on strings
[ 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 5:01 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 the same table, OC_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 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 > 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.
[jira] Issue Comment Edited: (DERBY-4422) Extremely slow subqueries when subquerying on strings
[ 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.
[jira] Issue Comment Edited: (DERBY-4422) Extremely slow subqueries when subquerying on strings
[ 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:51 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 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 was (Author: apb): Knut, Thanks for looking into this. Unfortunately, the query that is giving the problem 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.
[jira] Issue Comment Edited: (DERBY-4422) Extremely slow subqueries when subquerying on strings
[ https://issues.apache.org/jira/browse/DERBY-4422?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12770643#action_12770643 ] Tony Brusseau edited comment on DERBY-4422 at 10/27/09 10:29 PM: - When the problematic query is separated into 2 separate queries, each separate query runs extremely fast: SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash' SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (23146, 97675, 105192, 127143)) However, when asked in subquery form: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash')) the query takes over 6 seconds to run. According to The Practical SQL Handbook, noncorrelated queries are evaluated from the inside out which should make the performance between the two schemes above equivalent, however, I'm experiencing ~4 orders of magnitude difference in performance. was (Author: apb): When the problematic query is separated into 2 separate queries, each separate query runs extremely fast: SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash' SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (23146, 97675, 105192, 127143)) However, when asked in subquery form: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash')) the query takes over 6 seconds to run. According to The Practical SQL Handbook, noncorrelated queries are evaluated from the inside out which should make the performance between the two schemes above equivalent, however, I'm experiencing ~4 order of magnitude difference in performance. > 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: derby.log, fixed-time-derby.log, 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.
[jira] Issue Comment Edited: (DERBY-4422) Extremely slow subqueries when subquerying on strings
[ https://issues.apache.org/jira/browse/DERBY-4422?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12770643#action_12770643 ] Tony Brusseau edited comment on DERBY-4422 at 10/27/09 10:28 PM: - When the problematic query is separated into 2 separate queries, each separate query runs extremely fast: SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash' SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (23146, 97675, 105192, 127143)) However, when asked in subquery form: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash')) the query takes over 6 seconds to run. According to The Practical SQL Handbook, noncorrelated queries are evaluated from the inside out which should make the performance between the two schemes above equivalent, however, I'm experiencing ~4 order of magnitude difference in performance. was (Author: apb): When the problematic query is separated into 2 separate queries, each separate query runs extremely fast: SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash' SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (23146, 97675, 105192, 127143)) However, when asked in subquery form: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash')) the query takes over 6 seconds to run. According to The Practical SQL Handbook, noncorrelated queries are evaluated from the inside out which should make the performance between the two schemes equivalent. > 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: derby.log, fixed-time-derby.log, 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.
[jira] Issue Comment Edited: (DERBY-4422) Extremely slow subqueries when subquerying on strings
[ https://issues.apache.org/jira/browse/DERBY-4422?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12770643#action_12770643 ] Tony Brusseau edited comment on DERBY-4422 at 10/27/09 10:24 PM: - When the problematic query is separated into 2 separate queries, each separate query runs extremely fast: SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash' SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (23146, 97675, 105192, 127143)) However, when asked in subquery form: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash')) the query takes over 6 seconds to run. According to The Practical SQL Handbook, noncorrelated queries are evaluated from the inside out which should make the performance between the two schemes equivalent. was (Author: apb): When the problematic query is separated into 2 separate queries, each separate query runs extremely fast: SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash' SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (23146, 97675, 105192, 127143)) However, when asked in subquery form: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash')) the query takes over 6 seconds to run. > 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: derby.log, fixed-time-derby.log, 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.
[jira] Issue Comment Edited: (DERBY-4422) Extremely slow subqueries when subquerying on strings
[ https://issues.apache.org/jira/browse/DERBY-4422?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12770643#action_12770643 ] Tony Brusseau edited comment on DERBY-4422 at 10/27/09 9:22 PM: When the problematic query is separated into 2 separate queries, each separate query runs extremely fast: SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash' SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (23146, 97675, 105192, 127143)) However, when asked in subquery form: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash')) the query takes over 6 seconds to run. was (Author: apb): When the problematic query is separated into 2 separate queries, each separate query runs extremely fast: (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash') SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (23146, 97675, 105192, 127143)) However, when asked in subquery form: SELECT DISTINCT INTERNAL_ID FROM OC_CONCEPTS WHERE (INTERNAL_ID IN (SELECT DISTINCT CONCEPT_ID FROM OC_CONCEPT_STRINGS WHERE NL_LC = 'dash')) the query takes over 6 seconds to run. > 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: derby.log, fixed-time-derby.log, 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.
