GridGain Community Edition

Hi Team,

We have started doing a POC to deploy an existing application using Ignite as the in memory cache. There are couple of queries which we have changed a bit to make them work in ignite but when it comes to joins, we see performance issues. When we try joining two tables(a, b) and filter using (a.name=b.name), we observe that the filter is not being applied as it should(there is an index created on the column (name)). It is trying to fetch all columns of both the tables and then map the data based on the filter. This is fine if the tables have smaller data set but if the tables are huge and if we try joining more than 2 tables, there is a huge performance issue that we see. 

The error we see in ignite logs is (Query execution is too long [duration=16285ms, type=MAP, distributedJoin=false, enforceJoinOrder=false, lazy=false) when we try joining three big tables with filters applied

Could you please suggest if there is an ideal way in which joins work in Ignite with a sample and also enlighten us if there is any optimization that can be performed to achieve better response time.

Thanks,
Anil

------------------------------
Anil Kumar
Developer
Tata Consultancy Services
------------------------------

Hello!

Can you please specify the query in question, its DDL/cache configuration, and all rows of the EXPLAIN <query> statement execution on your cluster?

Regards,

------------------------------
Ilya Kasnacheev
Community Support Specialist
GridGain
------------------------------

Original Message:
Sent: 01-03-2021 07:02 PM
From: Anil Kumar
Subject: Ignite Joins

Hi Team,

We have started doing a POC to deploy an existing application using Ignite as the in memory cache. There are couple of queries which we have changed a bit to make them work in ignite but when it comes to joins, we see performance issues. When we try joining two tables(a, b) and filter using (a.name=b.name), we observe that the filter is not being applied as it should(there is an index created on the column (name)). It is trying to fetch all columns of both the tables and then map the data based on the filter. This is fine if the tables have smaller data set but if the tables are huge and if we try joining more than 2 tables, there is a huge performance issue that we see. 

The error we see in ignite logs is (Query execution is too long [duration=16285ms, type=MAP, distributedJoin=false, enforceJoinOrder=false, lazy=false) when we try joining three big tables with filters applied

Could you please suggest if there is an ideal way in which joins work in Ignite with a sample and also enlighten us if there is any optimization that can be performed to achieve better response time.

Thanks,
Anil

------------------------------
Anil Kumar
Developer
Tata Consultancy Services
------------------------------

Hi Ilya,

Thanks for the reply. Please find the query and plan attached for the same.

Thanks,
Anil

------------------------------
Anil Kumar
Developer
Tata Consultancy Services
------------------------------

Original Message:
Sent: 01-04-2021 01:00 AM
From: Ilya Kasnacheev
Subject: Ignite Joins

Hello!

Can you please specify the query in question, its DDL/cache configuration, and all rows of the EXPLAIN <query> statement execution on your cluster?

Regards,

------------------------------
Ilya Kasnacheev
Community Support Specialist
GridGain

Original Message:
Sent: 01-03-2021 07:02 PM
From: Anil Kumar
Subject: Ignite Joins

Hi Team,

We have started doing a POC to deploy an existing application using Ignite as the in memory cache. There are couple of queries which we have changed a bit to make them work in ignite but when it comes to joins, we see performance issues. When we try joining two tables(a, b) and filter using (a.name=b.name), we observe that the filter is not being applied as it should(there is an index created on the column (name)). It is trying to fetch all columns of both the tables and then map the data based on the filter. This is fine if the tables have smaller data set but if the tables are huge and if we try joining more than 2 tables, there is a huge performance issue that we see. 

The error we see in ignite logs is (Query execution is too long [duration=16285ms, type=MAP, distributedJoin=false, enforceJoinOrder=false, lazy=false) when we try joining three big tables with filters applied

Could you please suggest if there is an ideal way in which joins work in Ignite with a sample and also enlighten us if there is any optimization that can be performed to achieve better response time.

Thanks,
Anil

------------------------------
Anil Kumar
Developer
Tata Consultancy Services
------------------------------

Hello!

I can see that index on P_C_GRID is not selective:
INNER JOIN P_C_GRID C__Z2
/* P_C_GRID_BC_IDX: BTYPE IN('A', 'B')
AND CN = A__Z0.ATTRIBUTE_VALUE
*/
ON 1=1
/* WHERE (C__Z2.BTYPE IN('A', 'B'))
AND (C__Z2.CN = A__Z0.ATTRIBUTE_VALUE)
*/
/* scanCount: 11926420 */

Which indexes do you have defined on that table?

Regards.

------------------------------
Ilya Kasnacheev
Community Support Specialist
GridGain
------------------------------

Original Message:
Sent: 01-04-2021 01:40 AM
From: Anil Kumar
Subject: Ignite Joins

Hi Ilya,

Thanks for the reply. Please find the query and plan attached for the same.

Thanks,
Anil

------------------------------
Anil Kumar
Developer
Tata Consultancy Services

Original Message:
Sent: 01-04-2021 01:00 AM
From: Ilya Kasnacheev
Subject: Ignite Joins

Hello!

Can you please specify the query in question, its DDL/cache configuration, and all rows of the EXPLAIN <query> statement execution on your cluster?

Regards,

------------------------------
Ilya Kasnacheev
Community Support Specialist
GridGain

Original Message:
Sent: 01-03-2021 07:02 PM
From: Anil Kumar
Subject: Ignite Joins

Hi Team,

We have started doing a POC to deploy an existing application using Ignite as the in memory cache. There are couple of queries which we have changed a bit to make them work in ignite but when it comes to joins, we see performance issues. When we try joining two tables(a, b) and filter using (a.name=b.name), we observe that the filter is not being applied as it should(there is an index created on the column (name)). It is trying to fetch all columns of both the tables and then map the data based on the filter. This is fine if the tables have smaller data set but if the tables are huge and if we try joining more than 2 tables, there is a huge performance issue that we see. 

The error we see in ignite logs is (Query execution is too long [duration=16285ms, type=MAP, distributedJoin=false, enforceJoinOrder=false, lazy=false) when we try joining three big tables with filters applied

Could you please suggest if there is an ideal way in which joins work in Ignite with a sample and also enlighten us if there is any optimization that can be performed to achieve better response time.

Thanks,
Anil

------------------------------
Anil Kumar
Developer
Tata Consultancy Services
------------------------------

Hi Ilya,

We have below indexes created on the mentioned tables.

Table P_C_GRID(176770 rows present in this table):
CREATE INDEX P_C_GRID_BC_IDX ON P_C_GRID (BTYPE, CN);
CREATE INDEX P_C_GRID_M7 ON P_C_GRID (PN ASC);

Table C_GRID(1329 rows are present in this table):
CREATE INDEX C_GRID_C_IDX ON C_GRID (CN ASC);

Table INB_TEMP(This is a input table. Rows can vary basis the request for one inb_id. Min:1 row, Max:600 rows):
CREATE INDEX INB_TEMP_M1 ON INB_TEMP_M1 (INB_ID ASC, ATTRIBUTE_NAME ASC, ACTIVE ASC) ;

Thanks,
Anil

------------------------------
Anil Kumar
Developer
Tata Consultancy Services
------------------------------

Original Message:
Sent: 01-04-2021 02:10 AM
From: Ilya Kasnacheev
Subject: Ignite Joins

Hello!

I can see that index on P_C_GRID is not selective:
INNER JOIN P_C_GRID C__Z2
/* P_C_GRID_BC_IDX: BTYPE IN('A', 'B')
AND CN = A__Z0.ATTRIBUTE_VALUE
*/
ON 1=1
/* WHERE (C__Z2.BTYPE IN('A', 'B'))
AND (C__Z2.CN = A__Z0.ATTRIBUTE_VALUE)
*/
/* scanCount: 11926420 */

Which indexes do you have defined on that table?

Regards.

------------------------------
Ilya Kasnacheev
Community Support Specialist
GridGain

Original Message:
Sent: 01-04-2021 01:40 AM
From: Anil Kumar
Subject: Ignite Joins

Hi Ilya,

Thanks for the reply. Please find the query and plan attached for the same.

Thanks,
Anil

------------------------------
Anil Kumar
Developer
Tata Consultancy Services

Original Message:
Sent: 01-04-2021 01:00 AM
From: Ilya Kasnacheev
Subject: Ignite Joins

Hello!

Can you please specify the query in question, its DDL/cache configuration, and all rows of the EXPLAIN <query> statement execution on your cluster?

Regards,

------------------------------
Ilya Kasnacheev
Community Support Specialist
GridGain

Original Message:
Sent: 01-03-2021 07:02 PM
From: Anil Kumar
Subject: Ignite Joins

Hi Team,

We have started doing a POC to deploy an existing application using Ignite as the in memory cache. There are couple of queries which we have changed a bit to make them work in ignite but when it comes to joins, we see performance issues. When we try joining two tables(a, b) and filter using (a.name=b.name), we observe that the filter is not being applied as it should(there is an index created on the column (name)). It is trying to fetch all columns of both the tables and then map the data based on the filter. This is fine if the tables have smaller data set but if the tables are huge and if we try joining more than 2 tables, there is a huge performance issue that we see. 

The error we see in ignite logs is (Query execution is too long [duration=16285ms, type=MAP, distributedJoin=false, enforceJoinOrder=false, lazy=false) when we try joining three big tables with filters applied

Could you please suggest if there is an ideal way in which joins work in Ignite with a sample and also enlighten us if there is any optimization that can be performed to achieve better response time.

Thanks,
Anil

------------------------------
Anil Kumar
Developer
Tata Consultancy Services
------------------------------

Hello!

Try to redefine the index:

CREATE INDEX P_C_GRID_BC_IDX ON P_C_GRID (CN, BTYPE);

Otherwise, index joins cannot be performed on this table by CN field. Maybe you should get rid of BTYPE entirely in this index.

Regards,

------------------------------
Ilya Kasnacheev
Community Support Specialist
GridGain
------------------------------

Original Message:
Sent: 01-04-2021 04:57 AM
From: Anil Kumar
Subject: Ignite Joins

Hi Ilya,

We have below indexes created on the mentioned tables.

Table P_C_GRID(176770 rows present in this table):
CREATE INDEX P_C_GRID_BC_IDX ON P_C_GRID (BTYPE, CN);
CREATE INDEX P_C_GRID_M7 ON P_C_GRID (PN ASC);

Table C_GRID(1329 rows are present in this table):
CREATE INDEX C_GRID_C_IDX ON C_GRID (CN ASC);

Table INB_TEMP(This is a input table. Rows can vary basis the request for one inb_id. Min:1 row, Max:600 rows):
CREATE INDEX INB_TEMP_M1 ON INB_TEMP_M1 (INB_ID ASC, ATTRIBUTE_NAME ASC, ACTIVE ASC) ;

Thanks,
Anil

------------------------------
Anil Kumar
Developer
Tata Consultancy Services

Original Message:
Sent: 01-04-2021 02:10 AM
From: Ilya Kasnacheev
Subject: Ignite Joins

Hello!

I can see that index on P_C_GRID is not selective:
INNER JOIN P_C_GRID C__Z2
/* P_C_GRID_BC_IDX: BTYPE IN('A', 'B')
AND CN = A__Z0.ATTRIBUTE_VALUE
*/
ON 1=1
/* WHERE (C__Z2.BTYPE IN('A', 'B'))
AND (C__Z2.CN = A__Z0.ATTRIBUTE_VALUE)
*/
/* scanCount: 11926420 */

Which indexes do you have defined on that table?

Regards.

------------------------------
Ilya Kasnacheev
Community Support Specialist
GridGain

Original Message:
Sent: 01-04-2021 01:40 AM
From: Anil Kumar
Subject: Ignite Joins

Hi Ilya,

Thanks for the reply. Please find the query and plan attached for the same.

Thanks,
Anil

------------------------------
Anil Kumar
Developer
Tata Consultancy Services

Original Message:
Sent: 01-04-2021 01:00 AM
From: Ilya Kasnacheev
Subject: Ignite Joins

Hello!

Can you please specify the query in question, its DDL/cache configuration, and all rows of the EXPLAIN <query> statement execution on your cluster?

Regards,

------------------------------
Ilya Kasnacheev
Community Support Specialist
GridGain

Original Message:
Sent: 01-03-2021 07:02 PM
From: Anil Kumar
Subject: Ignite Joins

Hi Team,

We have started doing a POC to deploy an existing application using Ignite as the in memory cache. There are couple of queries which we have changed a bit to make them work in ignite but when it comes to joins, we see performance issues. When we try joining two tables(a, b) and filter using (a.name=b.name), we observe that the filter is not being applied as it should(there is an index created on the column (name)). It is trying to fetch all columns of both the tables and then map the data based on the filter. This is fine if the tables have smaller data set but if the tables are huge and if we try joining more than 2 tables, there is a huge performance issue that we see. 

The error we see in ignite logs is (Query execution is too long [duration=16285ms, type=MAP, distributedJoin=false, enforceJoinOrder=false, lazy=false) when we try joining three big tables with filters applied

Could you please suggest if there is an ideal way in which joins work in Ignite with a sample and also enlighten us if there is any optimization that can be performed to achieve better response time.

Thanks,
Anil

------------------------------
Anil Kumar
Developer
Tata Consultancy Services
------------------------------