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 <marcel.f...@gmail.com> 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/ >