Just to add one more finding.
If I execute my query in one piece, runtime is 10 to 15 seconds (for
updating 1252 rows)
UPDATE PUBLIC.AAA_STR_HIE_ACCOUNT
SET MBR_TYPE = 'B'
WHERE ID IN (
SELECT DISTINCT t1.ID
FROM PUBLIC.AAA_STR_HIE_ACCOUNT t1
LEFT JOIN PUBLIC.AAA_STR_HIE_ACCOUNT t2 ON t2.PARENT = t1.ID AND t2.hier
= t1.hier
WHERE t2.PARENT IS NULL
AND t1.hier = 'H4'
)
AND hier='H4';
But if I execute both queries separately, so first the subquery.
SELECT DISTINCT t1.ID AS ID
FROM PUBLIC.AAA_STR_HIE_ACCOUNT t1
LEFT JOIN PUBLIC.AAA_STR_HIE_ACCOUNT t2 ON t2.PARENT = t1.ID AND t2.hier =
t1.hier
WHERE t2.PARENT IS NULL
AND t1.hier = 'H4';
and then pass the result to the main query as a list (only showing 3
values, query has 1252 values
UPDATE PUBLIC.AAA_STR_HIE_ACCOUNT
SET MBR_TYPE = 'B'
WHERE ID IN ('36101068','38155540',...,'80000079')
AND hier = 'H4';
I got a total execution time of less than 2 seconds.
I don't really understand the huge gap, I would expect the single query to
be faster than 2 separate queries.
On Mon, 10 Jun 2019 at 16:42, mfrey <[email protected]> wrote:
> Hi, is there a plan to implement this (join on UPDATE) or to improve the
> performance of the "IN" operator ?
>
> I have an UPDATE query with two "IN" operators.
> UPDATE PUBLIC.AAA_STR_HIE_ACCOUNT
> SET MBR_TYPE = 'B'
> WHERE ID IN (
> SELECT ID
> ...'
> AND ID NOT IN (
> SELECT PARENT
> ...
> )
> );
>
> I have 1700 rows and I stopped the query after 5 minutes
>
> If the IN in the subquery with a join, I get a runtime of about 10
> second.
>
> UPDATE PUBLIC.AAA_STR_HIE_ACCOUNT AS t3
> SET MBR_TYPE = 'B'
> WHERE ID IN (SELECT DISTINCT t1.ID
> FROM PUBLIC.AAA_STR_HIE_ACCOUNT t1
> LEFT JOIN PUBLIC.AAA_STR_HIE_ACCOUNT t2 ON t2.PARENT = t1.ID
> WHERE t2.PARENT IS NULL
> )
>
>
> The runtime of the subquery went down from 3.7s to 17ms. How can I get rid
> of the first "IN" to speed up my whole query ?
>
>
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
