UPDATE query with JOIN

classic Classic list List threaded Threaded
8 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/