db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6939) Update statement poor performance with index using where in (select
Date Tue, 06 Jun 2017 01:58:19 GMT

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

Rick Hillegas commented on DERBY-6939:
--------------------------------------

Hi Jim,

Have you analyzed the query plan using RUNTIMESTATISTICS as described in the section on that
topic in the Derby Tuning Guide? If you attach the query plan, we can speculate about what
the problem is.

Thanks,
-Rick

> Update statement poor performance with index using where in (select
> -------------------------------------------------------------------
>
>                 Key: DERBY-6939
>                 URL: https://issues.apache.org/jira/browse/DERBY-6939
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.11.1.1
>            Reporter: Jim Cross
>
> An update statement such as below doesn't appear to be using the index:
> update mytable set col1 = 'myvalue' where indexed_col in (select index_col from mytable
where col1 like 'myv%' fetch first 100 rows only);
> The table in this case has over a million rows. Running the sub-query:
> select index_col from mytable where col1 like 'myv%' fetch first 100 rows only
> Returns almost immediately with 100 rows but this update takes over two minutes. Update
does not seem to be using index because originally we try the query without the index and
performance after adding the index seemed to be the same.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

Mime
View raw message