GridGain Community Edition

Expand all | Collapse all

Ignite Joins

  • 1.  Ignite Joins

    Posted 18 days ago
    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
    ------------------------------


  • 2.  RE: Ignite Joins

    Posted 18 days ago
    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
    ------------------------------



  • 3.  RE: Ignite Joins

    Posted 18 days ago
    Hi Ilya,

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

    Thanks,
    Anil

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

    Attachment(s)

    txt
    Query1.txt   473 B 1 version
    txt
    Log1_with_plan.txt   2 KB 1 version


  • 4.  RE: Ignite Joins

    Posted 18 days ago
    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
    ------------------------------



  • 5.  RE: Ignite Joins

    Posted 18 days ago
    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
    ------------------------------



  • 6.  RE: Ignite Joins

    Posted 18 days ago
    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
    ------------------------------