Hi,
Whenever I use a String field (indexed) in the join condition of a
LEFT/RIGHT OUTER JOIN, the query takes a lot of time (seconds) to return the
result. However, this does not happen when a Long field is used. Please let
me know if I'm missing something here.
The fields are described as below in the RawMessage class:
@QuerySqlField(index = true)
public long id;
@QuerySqlField(index = true)
public String OrderID;
@QuerySqlField
public String LatestClOrdID;
And this is the query:
select l.latestClOrdID, l.OrderID, r.latestClOrdID, r.orderID " +
"from RawMessage as l LEFT OUTER JOIN \"" + RIGHT_CACHE + "\".RawMessage as
r " +
"on l.orderID = r.orderID ";
The query returns in a few millis on a date of 10000 records in the cache,
when id is used. However, it takes more than a second when orderID is used.
EXPLAIN PLAN shows following:
[SELECT
L__Z0.LATESTCLORDID AS __C0_0,
L__Z0.ORDERID AS __C0_1,
R__Z1.LATESTCLORDID AS __C0_2,
R__Z1.ORDERID AS __C0_3
FROM "LeftCache".RAWMESSAGE L__Z0
/* "LeftCache".RAWMESSAGE.__SCAN_ */
LEFT OUTER JOIN "RightCache".RAWMESSAGE R__Z1
/* batched:broadcast "RightCache".RAWMESSAGE_ORDERID_IDX: ORDERID =
L__Z0.ORDERID */
ON L__Z0.ORDERID = R__Z1.ORDERID]
[SELECT
__C0_0 AS LATESTCLORDID,
__C0_1 AS ORDERID,
__C0_2,
__C0_3
FROM PUBLIC.__T0
/* "LeftCache"."merge_scan" */]
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
