UPDATE query with JOIN

classic Classic list List threaded Threaded
12 messages Options
iostream iostream
Reply | Threaded
Open this post in threaded view
|

UPDATE query with JOIN

This post was updated on .
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<Integer> 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&lt;?>> 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<Integer> 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/
Alexander Paschenko Alexander Paschenko
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE query with JOIN

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 <[hidden email]>:

> 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<Integer> 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&lt;?>> 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<Integer> 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/
iostream iostream
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE query with JOIN

Hi Alexander,

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





--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Justin Ji Justin Ji
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE query with JOIN

In reply to this post by Alexander Paschenko
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/
Justin Ji Justin Ji
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE query with JOIN

In reply to this post by Alexander Paschenko
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/
ezhuravlev ezhuravlev
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE query with JOIN

Hi,

Are you sure that you have index for devId field?

Evgenii

пн, 8 окт. 2018 г. в 12:23, Justin Ji <[hidden email]>:
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/
Justin Ji Justin Ji
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE query with JOIN

This post was updated on .
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', '00200002005043011111',
'vdevp150509677704164', '002yt001sf000000sf0q', '6c2dd83eebd2723329ornu',
'6ce091736ee2cdef6c2gjc', '6c7510b6d2b42b9a46w9j3', '002yt001sf000000sfrz',
'6c05c274a04cca4e00z1tp', '6c6baec455eac8bd17ozfn', '002yt001sf0000gwsfV3')
*/
WHERE __Z0.DEVID IN('1002', '6c072f7d599215dadfs0ya',
'6cdff0d13a96d8cec0j8v6', '6cf3cde4012b74b853qsoe',
'6c0d48eb1718840a69yndq', '00200002005043011111', 'vdevp150509677704164',
'002yt001sf000000sf0q', '6c2dd83eebd2723329ornu', '6ce091736ee2cdef6c2gjc',
'6c7510b6d2b42b9a46w9j3', '002yt001sf000000sfrz', '6c05c274a04cca4e00z1tp',
'6c6baec455eac8bd17ozfn', '002yt001sf0000gwsfV3')]
[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<QueryEntity> entities = getQueryEntities();
cacheCfg.setQueryEntities(entities);

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

        QueryEntity entity = new QueryEntity(String.class.getName(),
DeviceStatusIgniteVO.class.getName());
       
entity.setTableName(IgniteTableKey.T_DEVICE_ONLINE_STATUS.getCode());

        LinkedHashMap<String, String> 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<QueryIndex> indexes = Lists.newArrayList(new
QueryIndex("devId"));
        entity.setIndexes(indexes);

        entities.add(entity);

        return entities;
    }



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Justin Ji Justin Ji
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE query with JOIN

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/
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE query with JOIN

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/
mfrey mfrey
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE query with JOIN

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/
mfrey mfrey
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE query with JOIN

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 <[hidden email]> 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/
Maxim.Pudov Maxim.Pudov
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE query with JOIN

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/