Re: UPDATE query with JOIN

[Maxim.Pudov]

Hey, something is wrong in your sample
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';

The resultset will be either empty or one row with null in it, but you said
it's 1252 rows.
if t2.PARENT = t1.ID and t2.PARENT IS NULL then t1.ID is always null.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: UPDATE query with JOIN

[Marcel Frey]

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',...,'8079')
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  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/
>

Re: UPDATE query with JOIN

[mfrey]

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/

Re: UPDATE query with JOIN

[ilya.kasnacheev]

Hello!

Unfortunately you can't do this right now. Your best bet is 4) as you have
figured.

Regards,



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: UPDATE query with JOIN

[Justin Ji]

Anyone who can tell me how to do an update operation with *join table*?

I have tried the SQL below:
1.update A set A.a1=1 join table(devId varchar=?) B on A.devId=B.devId *SQL
ERROR*
2.update A, table(devId varchar=?) B set A.a2=1 where A.devId=B.devId *SQL
ERROR*
3.update A set A.a1=1 where A.devId in (select table.devId from table(devId
varchar = ?)) *amost 2 second with 2048 records*
4.update A set A.a1=1 where A.devId in ('1', '2', ..., '2408') *about 600ms
with 2048 records*

It seemed that h2 database does not support first two SQL according to my
test.

And the third SQL has worse performance than the forth, I also do not know
why.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: UPDATE query with JOIN

[Justin Ji]

Thank for your reply!
I sure I have indexed the devId, here is the output of EXPLAIN:

[SELECT
__Z0.DEVID AS __C0_0,
__Z0.ISONLINE AS __C0_1,
__Z0.MQTTTIME AS __C0_2
FROM "device_online_status".T_DEVICE_ONLINE_STATUS __Z0
/* "device_online_status".T_DEVICE_ONLINE_STATUS_DEVID_ASC_IDX: DEVID
IN('1002', '6c072f7d599215dadfs0ya', '6cdff0d13a96d8cec0j8v6',
'6cf3cde4012b74b853qsoe', '6c0d48eb1718840a69yndq', '002200504301',
'vdevp150509677704164', '002yt001sf00sf0q', '6c2dd83eebd2723329ornu',
'6ce091736ee2cdef6c2gjc', '6c7510b6d2b42b9a46w9j3', '002yt001sf00sfrz',
'6c05c274a04cca4e00z1tp', '6c6baec455eac8bd17ozfn', '002yt001sfgwsfV3')
*/
WHERE __Z0.DEVID IN('1002', '6c072f7d599215dadfs0ya',
'6cdff0d13a96d8cec0j8v6', '6cf3cde4012b74b853qsoe',
'6c0d48eb1718840a69yndq', '002200504301', 'vdevp150509677704164',
'002yt001sf00sf0q', '6c2dd83eebd2723329ornu', '6ce091736ee2cdef6c2gjc',
'6c7510b6d2b42b9a46w9j3', '002yt001sf00sfrz', '6c05c274a04cca4e00z1tp',
'6c6baec455eac8bd17ozfn', '002yt001sfgwsfV3')]
[SELECT
__C0_0 AS DEVID,
__C0_1 AS ISONLINE,
__C0_2 AS MQTTTIME
FROM PUBLIC.__T0
/* "device_online_status"."merge_scan" */]

And the following code is the way that I create a cache:


List entities = getQueryEntities();
cacheCfg.setQueryEntities(entities);

private List getQueryEntities() {
List entities = Lists.newArrayList();

//配置可见(可被查询)字段
QueryEntity entity = new QueryEntity(String.class.getName(),
DeviceStatusIgniteVO.class.getName());
   
entity.setTableName(IgniteTableKey.T_DEVICE_ONLINE_STATUS.getCode());

LinkedHashMap map = new LinkedHashMap<>();
map.put("devId", "java.lang.String");
map.put("isOnline", "java.lang.Boolean");
map.put("gmtModified", "java.lang.Long");
map.put("mqttTime", "java.lang.Long");
entity.setFields(map);

//配置索引信息
List indexes = Lists.newArrayList(new
QueryIndex("devId"));
entity.setIndexes(indexes);

entities.add(entity);

return entities;
}



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: UPDATE query with JOIN

[Evgenii Zhuravlev]

Hi,

Are you sure that you have index for devId field?

Evgenii

пн, 8 окт. 2018 г. в 12:23, Justin Ji :

> Hi Alexander -
>
> I have tried the SQL you suggested, but the performance got worse, I do not
> know why?
>
> 1. "update t_device_module set isOnline=1, mqttTime=" +
> System.currentTimeMillis() / 1000 + " where devId in ('0001', '0002', ...,
> '2048')";
> The SQL may take 600ms for 2048 records.
>
> 2."update t_device_module t1 set t1.isOnline=1, t1.mqttTime=" +
> System.currentTimeMillis() / 1000 + " where t1.devId in (select table.devId
> from table(devId varchar = ?))";
>
> cache.query(new SqlFieldsQuery(sql2).setArgs(new
> Object[]{clientIds.toArray()}));
>
> The clientIds is a list which contains 2048 records(equals with above), but
> it was executed about 2000ms.
>
> According to the introduction of
> sql-performance-and-usability-considerations
> <
> https://apacheignite-sql.readme.io/docs/performance-and-debugging#sql-performance-and-usability-considerations>
>
> : the second SQL is recommended, bucause the first one will not use
> indexes.
>
> My question is why the second one is worse than the first one.
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>

Re: UPDATE query with JOIN

[Justin Ji]

Hi Alexander - 

I have tried the SQL you suggested, but the performance got worse, I do not
know why? 

1. "update t_device_module set isOnline=1, mqttTime=" +
System.currentTimeMillis() / 1000 + " where devId in ('0001', '0002', ...,
'2048')";
The SQL may take 600ms for 2048 records.

2."update t_device_module t1 set t1.isOnline=1, t1.mqttTime=" +
System.currentTimeMillis() / 1000 + " where t1.devId in (select table.devId
from table(devId varchar = ?))";

cache.query(new SqlFieldsQuery(sql2).setArgs(new
Object[]{clientIds.toArray()}));

The clientIds is a list which contains 2048 records(equals with above), but
it was executed about 2000ms.

According to the introduction of 
sql-performance-and-usability-considerations

  
: the second SQL is recommended, bucause the first one will not use indexes.

My question is why the second one is worse than the first one.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: UPDATE query with JOIN

[Justin Ji]

Hi Alexander - 

I have tried the SQL you suggested, but the performance got worse, I do not
know why? 

1. "update t_device_module set isOnline=1, mqttTime=" +
System.currentTimeMillis() / 1000 + " where devId in ('0001', '0002', ...,
'2048')";
The SQL may take 600ms for 2048 records.

2."update t_device_module t1 set t1.isOnline=1, t1.mqttTime=" +
System.currentTimeMillis() / 1000 + " where t1.devId in (select table.devId
from table(devId varchar = ?))";

cache.query(new SqlFieldsQuery(sql2).setArgs(new
Object[]{clientIds.toArray()}));

The clientIds is a list which contains 2048 records(equals with above), but
it was executed about 2000ms.

According to the introduction of 
sql-performance-and-usability-considerations

  
: the second SQL is recommended, bucause the first one will not use indexes.

My question is why the second one is worse than the first one.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: UPDATE query with JOIN

[iostream]

Hi Alexander,

I will try what you have suggested. Thanks for the suggestions!





--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: UPDATE query with JOIN

[Alexander Paschenko]

Hello Sidds,

UPDATE with JOINs is not supported in Ignite as long as H2 database
grammar Ignite relies on does not support it too [1]. AFAIK, they are
not part of ANSI SQL 99 either, and thus different DB engines have
different syntax and capabilities to support it. However, I believe I
understand what you're trying to do and would like to give you a good
workaround.

Could you please explain what are you trying to achieve? It looks like
by this join you're trying to additionally limit some range of status
codes that you want to be affected, is that right? If it is, I'd
recommend you to use IN instead of JOIN, here's what your query would
look like in this case:

update fulfill_order fo

set fo.fulfill_order_status_code =?, fo.last_update_userid =?,
fo.order_due_ts =?, fo.last_update_ts =?

where fo.fulfill_order_id=?

AND fo.fulfill_order_status_code IN ( SELECT TABLE.STATUS FROM TABLE(STATUS=?) )

Note that last line written in caps after AND - it introduces a fake
subselect query. Thus, you'll need to supply your array of filtering
args as the LAST parameter of this query. (You call corresponding
variable "list" in your code.)

Regards,
Alex

[1] http://www.h2database.com/html/grammar.html#update

2017-09-12 13:41 GMT+03:00 iostream <sidds.mo...@gmail.com>:
> Hi,
>
> Does ignite v2.1 support UPDATE queries with JOINS?
>
> I tried a SELECT query as follows and it worked fine -
>
> select count(*) from fulfill_order fo join table(id bigint = ?) t on
> fo.fulfill_order_status_code = t.id";
> SqlFieldsQuery enhanceQuery = new SqlFieldsQuery(cacheQuery);
> ArrayList list = new ArrayList<>();
> list.add(7);
> list.add(1);
> list.add(9);
> Integer [] arr = list.toArray(new Integer[list.size()]);
> Object [] obj = new Object[]{arr};
> enhanceQuery.setArgs(obj);
> IgniteCache<fulfill_order_key, fulfill_order> fulfillOrderCache =
> Ignition.ignite()
>
> .cache(CacheNameConstants.FULFILL_ORDER_CACHE_NAME);
> QueryCursor<List?>> cursor = fulfillOrderCache.query(enhanceQuery);
>
> However, I tried running UPDATE query as follows but none of the queries
> worked.
>
> 1. String updateQuery = "UPDATE fo SET fo.fulfill_order_status_code =? "
> + "FROM fulfill_order fo join table(id bigint = ?) t
> on fo.fulfill_order_status_code = t.id "
> + "where fo.fulfill_order_id=?";
> SqlFieldsQuery enhanceQuery = new SqlFieldsQuery(updateQuery);
> ArrayList list = new ArrayList<>();
> list.add(7);
> list.add(1);
> list.add(9);
> Integer [] arr = list.toArray(new Integer[list.size()]);
> Object [] obj = new Object[]{arr};
> enhanceQuery.setArgs(3,obj, 347427284695L);
> IgniteCache<fulfill_order_key, fulfill_order> fulfillOrderCache =
> Ignition.ignite()
>
> .cache(CacheNameConstants.FULFILL_ORDER_CACHE_NAME);
> fulfillOrderCache.query(enhanceQuery);
>
> 2. update fulfill_order fo join table(id bigint = ?) t on
> fo.fulfill_order_status_code = t.id
> set fo.fulfill_order_status_code =?, fo.last_update_userid =?,
> fo.order_due_ts =?,
> fo.last_update_ts =? where fo.fulfill_order_id=?
>
> Can someone help with the correct way of running UPDATE query with JOIN?
>
> Thanks!
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/

UPDATE query with JOIN

[iostream]

Hi,

Does ignite v2.1 support UPDATE queries with JOINS?

I tried a SELECT query as follows and it worked fine -

select count(*) from fulfill_order fo join table(id bigint = ?) t on
fo.fulfill_order_status_code = t.id";
SqlFieldsQuery enhanceQuery = new SqlFieldsQuery(cacheQuery);
ArrayList list = new ArrayList<>();
list.add(7);
list.add(1);
list.add(9);
Integer [] arr = list.toArray(new Integer[list.size()]);
Object [] obj = new Object[]{arr};
enhanceQuery.setArgs(obj);
IgniteCache<fulfill_order_key, fulfill_order> fulfillOrderCache =
Ignition.ignite()
   
.cache(CacheNameConstants.FULFILL_ORDER_CACHE_NAME);
QueryCursor<List?>> cursor = fulfillOrderCache.query(enhanceQuery);

However, I tried running UPDATE query as follows but none of the queries
worked.

1. String updateQuery = "UPDATE fo SET fo.fulfill_order_status_code =? "
+ "FROM fulfill_order fo join table(id bigint = ?) t
on fo.fulfill_order_status_code = t.id "
+ "where fo.fulfill_order_id=?";
SqlFieldsQuery enhanceQuery = new SqlFieldsQuery(updateQuery);
ArrayList list = new ArrayList<>();
list.add(7);
list.add(1);
list.add(9);
Integer [] arr = list.toArray(new Integer[list.size()]);
Object [] obj = new Object[]{arr};
enhanceQuery.setArgs(3,obj, 347427284695L);
IgniteCache<fulfill_order_key, fulfill_order> fulfillOrderCache =
Ignition.ignite()
   
.cache(CacheNameConstants.FULFILL_ORDER_CACHE_NAME);
fulfillOrderCache.query(enhanceQuery);   

2. update fulfill_order fo join table(id bigint = ?) t on
fo.fulfill_order_status_code = t.id
set fo.fulfill_order_status_code =?, fo.last_update_userid =?,
fo.order_due_ts =?,
fo.last_update_ts =? where fo.fulfill_order_id=?

Can someone help with the correct way of running UPDATE query with JOIN?

Thanks!



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/