ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Manish Mishra <man...@knoldus.com>
Subject Re: Getting Error [grid-timeout-worker] when running join query on Single Ignite Node
Date Fri, 30 Sep 2016 12:26:36 GMT
Thanks for reply.
@Taras  I have set QuerySqlField(index=true) on the indexing fields. @Alex,
I've used the EXPLAIN with query I make to Gridgain Cache but I don't know
how to verify if the indexes are invoked on not. Here is the output when I
used explain with the Query:

SELECT DISTINCT
M1 __C0,
M1_TYP __C1
FROM (SELECT
ENTRY AS A5,
QS5.A1 AS A1,
QS5.A4 AS A4,
QS5.M1 AS M1,
M1_TYP AS M1_TYP
FROM (SELECT
ENTITY AS M1,
TYP AS M1_TYP,
ELEM AS A5,
QS4.A1 AS A1,
QS4.A4 AS A4
FROM RS.RS T
 INNER JOIN (SELECT
ENTRY AS A2,
QS3.A1 AS A1,
QS3.A4 AS A4,
QS3.M1 AS M1,
M1_TYP AS M1_TYP
FROM (SELECT
ENTRY AS A4,
QS2.A1 AS A1,
QS2.A2 AS A2,
QS2.M1 AS M1,
M1_TYP AS M1_TYP
FROM (SELECT
ENTRY AS A3,
QS1.A1 AS A1,
QS1.A2 AS A2,
QS1.A4 AS A4,
QS1.M1 AS M1,
M1_TYP AS M1_TYP
FROM (SELECT
A1 AS A1,
M1 AS M1,
M1_TYP AS M1_TYP,
COALESCE(S2.ELEM, VAL2) AS A2,
COALESCE(S3.ELEM, VAL3) AS A3,
COALESCE(S4.ELEM, VAL4) AS A4
FROM (SELECT
ENTRY AS A1,
T.VAL2 AS M1,
T.TYP2 AS M1_TYP,
T.VAL8 AS VAL2,
T.VAL6 AS VAL3,
T.VAL6 AS VAL4
FROM (SELECT
ELEM AS A1
FROM RS.RS T
WHERE (PROP = '1oh~#has_neighbours') AND ((ENTITY = '3') AND (TYP = 5001)))
QS0
 INNER JOIN DPH.DPH T
 ON TRUE
WHERE (T.VAL0 = '7a~') AND ((ENTRY = QS0.A1) AND ((T.PROP2 =
'1oh~#is_atom_of') AND ((T.PROP6 = '1oh~#has_single_bond') AND ((T.PROP6 =
'1oh~#has_single_bond') AND ((T.PROP0 = '4xm~#type') AND (T.PROP8 =
'1oh~#has_double_bond'))))))) Q1
 LEFT OUTER JOIN DS.DS S2
 ON Q1.VAL2 = S2.LIST_ID
 LEFT OUTER JOIN DS.DS S3
 ON Q1.VAL3 = S3.LIST_ID
 LEFT OUTER JOIN DS.DS S4
 ON Q1.VAL4 = S4.LIST_ID
WHERE A1 <> COALESCE(S4.ELEM, VAL4)) QS1
 INNER JOIN DPH.DPH T
 ON TRUE
WHERE (T.VAL8 = '6o7~') AND ((T.VAL5 = '1') AND ((T.VAL0 = '562~') AND
((ENTRY = QS1.A3) AND ((T.PROP8 = '1oh~#has_charge') AND ((T.PROP0 =
'4xm~#type') AND (T.PROP5 = '1oh~#has_neighbours'))))))) QS2
 INNER JOIN DPH.DPH T
 ON TRUE
WHERE (T.VAL5 = '1') AND ((T.VAL0 = '7a~') AND ((ENTRY = QS2.A4) AND
((T.PROP0 = '4xm~#type') AND (T.PROP5 = '1oh~#has_neighbours'))))) QS3
 INNER JOIN DPH.DPH T
 ON TRUE
WHERE (T.VAL5 = '1') AND ((T.VAL0 = '562~') AND ((ENTRY = QS3.A2) AND
((T.PROP0 = '4xm~#type') AND (T.PROP5 = '1oh~#has_neighbours'))))) QS4
 ON TRUE
WHERE (PROP = '1oh~#is_atom_of') AND ((ENTITY = QS4.M1) AND (TYP =
QS4.M1_TYP))) QS5
 INNER JOIN DPH.DPH T
 ON TRUE
WHERE (T.VAL5 = '0') AND ((T.VAL0 = '1eg~') AND ((ENTRY = QS5.A5) AND
((T.PROP0 = '4xm~#type') AND (T.PROP5 = '1oh~#has_neighbours'))))) QS6
LIMIT 100"; expected "ANALYZE, PLAN, SELECT, FROM, (, DELETE, UPDATE,
INSERT, MERGE, WITH"; SQL statement:
EXPLAIN EXPLAIN SELECT DISTINCT
M1 __C0,
M1_TYP __C1
FROM (SELECT
ENTRY AS A5,
QS5.A1 AS A1,
QS5.A4 AS A4,
QS5.M1 AS M1,
M1_TYP AS M1_TYP
FROM (SELECT
ENTITY AS M1,
TYP AS M1_TYP,
ELEM AS A5,
QS4.A1 AS A1,
QS4.A4 AS A4
FROM RS.RS T
 INNER JOIN (SELECT
ENTRY AS A2,
QS3.A1 AS A1,
QS3.A4 AS A4,
QS3.M1 AS M1,
M1_TYP AS M1_TYP
FROM (SELECT
ENTRY AS A4,
QS2.A1 AS A1,
QS2.A2 AS A2,
QS2.M1 AS M1,
M1_TYP AS M1_TYP
FROM (SELECT
ENTRY AS A3,
QS1.A1 AS A1,
QS1.A2 AS A2,
QS1.A4 AS A4,
QS1.M1 AS M1,
M1_TYP AS M1_TYP
FROM (SELECT
A1 AS A1,
M1 AS M1,
M1_TYP AS M1_TYP,
COALESCE(S2.ELEM, VAL2) AS A2,
COALESCE(S3.ELEM, VAL3) AS A3,
COALESCE(S4.ELEM, VAL4) AS A4
FROM (SELECT
ENTRY AS A1,
T.VAL2 AS M1,
T.TYP2 AS M1_TYP,
T.VAL8 AS VAL2,
T.VAL6 AS VAL3,
T.VAL6 AS VAL4
FROM (SELECT
ELEM AS A1
FROM RS.RS T
WHERE (PROP = '1oh~#has_neighbours') AND ((ENTITY = '3') AND (TYP = 5001)))
QS0
 INNER JOIN DPH.DPH T
 ON TRUE
WHERE (T.VAL0 = '7a~') AND ((ENTRY = QS0.A1) AND ((T.PROP2 =
'1oh~#is_atom_of') AND ((T.PROP6 = '1oh~#has_single_bond') AND ((T.PROP6 =
'1oh~#has_single_bond') AND ((T.PROP0 = '4xm~#type') AND (T.PROP8 =
'1oh~#has_double_bond'))))))) Q1
 LEFT OUTER JOIN DS.DS S2
 ON Q1.VAL2 = S2.LIST_ID
 LEFT OUTER JOIN DS.DS S3
 ON Q1.VAL3 = S3.LIST_ID
 LEFT OUTER JOIN DS.DS S4
 ON Q1.VAL4 = S4.LIST_ID
WHERE A1 <> COALESCE(S4.ELEM, VAL4)) QS1
 INNER JOIN DPH.DPH T
 ON TRUE
WHERE (T.VAL8 = '6o7~') AND ((T.VAL5 = '1') AND ((T.VAL0 = '562~') AND
((ENTRY = QS1.A3) AND ((T.PROP8 = '1oh~#has_charge') AND ((T.PROP0 =
'4xm~#type') AND (T.PROP5 = '1oh~#has_neighbours'))))))) QS2
 INNER JOIN DPH.DPH T
 ON TRUE
WHERE (T.VAL5 = '1') AND ((T.VAL0 = '7a~') AND ((ENTRY = QS2.A4) AND
((T.PROP0 = '4xm~#type') AND (T.PROP5 = '1oh~#has_neighbours'))))) QS3
 INNER JOIN DPH.DPH T
 ON TRUE
WHERE (T.VAL5 = '1') AND ((T.VAL0 = '562~') AND ((ENTRY = QS3.A2) AND
((T.PROP0 = '4xm~#type') AND (T.PROP5 = '1oh~#has_neighbours'))))) QS4
 ON TRUE
WHERE (PROP = '1oh~#is_atom_of') AND ((ENTITY = QS4.M1) AND (TYP =
QS4.M1_TYP))) QS5
 INNER JOIN DPH.DPH T
 ON TRUE
WHERE (T.VAL5 = '0') AND ((T.VAL0 = '1eg~') AND ((ENTRY = QS5.A5) AND
((T.PROP0 = '4xm~#type') AND (T.PROP5 = '1oh~#has_neighbours'))))) QS6
LIMIT 100








On Thu, Sep 29, 2016 at 11:05 PM, Alexey Kuznetsov <akuznetsov@gridgain.com>
wrote:

> Alex,
>
> it is better to give a link to docs  when you are sugesting  EXPLAIN
> SELECT :)
>
> https://apacheignite.readme.io/docs/sql-queries#using-explain
>
> Hope this help :)
>
> --
> Alexey Kuznetsov
>



-- 
Thanks & Regard

Manish Mishra
Software Consultant,
Knoldus Software, LLP

Mime
View raw message