Hi Val,
I have created a complex query and looks good in terms of query plan. But
query execution time is very high.
query -
SELECT
P.serialnumber,
iP.count,
cnt.itemnumber,
cnt.status,
cnt.enddate
FROM Product P
left JOIN ( SELECT serialnumber, COUNT(*) AS count FROM Product
GROUP BY serialnumber ) iP
ON P.serialnumber = iP.serialnumber
JOIN contracts cnt
on P.equipmentid = cnt.equipmentid
where P.serialnumber = 's3'
and status = 'Active'
explain plan -
SELECT
IB.SERIALNUMBER,
IIB.COUNT,
CNT.ITEMNUMBER,
CNT.STATUS,
CNT.ENDDATE
FROM PUBLIC.PRODUCT P
* /* PUBLIC.SERNO_INDEX: SERIALNUMBER = 's3' */*
/* WHERE P.SERIALNUMBER = 's3'
*/
LEFT OUTER JOIN (
SELECT
SERIALNUMBER,
COUNT(*) AS COUNT
FROM PUBLIC.PRODUCT
GROUP BY SERIALNUMBER
) IP
/* SELECT
SERIALNUMBER,
COUNT(*) AS COUNT
FROM PUBLIC.PRODUCT
* /++ PUBLIC.SERNO_INDEX: SERIALNUMBER IS ?1 ++/*
WHERE SERIALNUMBER IS ?1
GROUP BY SERIALNUMBER
* /++ group sorted ++/: SERIALNUMBER = P.SERIALNUMBER*
*/
ON P.SERIALNUMBER = IP.SERIALNUMBER
INNER JOIN PUBLIC.CONTRACTS CNT
* /* PUBLIC.EQ_INDEX: EQUIPMENTID = P.EQUIPMENTID */*
ON P.EQUIPMENTID = CNT.EQUIPMENTID
WHERE (P.SERIALNUMBER = 's3')
AND (STATUS = 'Active')
Contracts are related to Product. So i created product Id reference in
Contract and annotated with @AffinityKeyMapped.
I feel that affinity is not happening. Do you see any issues with above
query or configuration ?
Thanks.
On 20 December 2016 at 02:11, vkulichenko <[email protected]>
wrote:
> I would also create indexes on 'id' fields at least. And do not forget to
> check the execution plan [1] to make sure everything works as expected.
>
> [1] http://apacheignite.gridgain.org/docs/performance-and-debugging
>
> -Val
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/Complex-queries-tp9626p9630.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
