Hi, I've looked closer to the query. First, it should be splitted by 3 sub-queries. Each sub-query must be optimized separately and joined by UNION ALL. Let's start from the first query. I've provided updated indexes for your model in the attachment. Please start server with enabled H2 console[1] and run there the following query after the data is loaded:
EXPLAIN ANALYZE SELECT DISTINCT * FROM activity activity0 LEFT OUTER JOIN "activityuseraccountrole".activityuseraccountrole activityuseraccountrole0 ON activityuseraccountrole0.activityId = activity0.activityId AND activityuseraccountrole0.useraccountroleId IN (1, 3) LEFT OUTER JOIN "activityhistory".activityhistory activityhistory0 ON activityhistory0.activityhistoryId = activity0.lastactivityId AND activityhistory0.activitystateEnumid NOT IN (37, 30, 463, 33, 464) LEFT OUTER JOIN "activityhistoryuseraccount".activityhistoryuseraccount activityhistoryuseraccount0 ON activityhistoryuseraccount0.activityHistoryId = activityhistory0.activityhistoryId WHERE activity0.kernelId IS NULL AND activity0.realizationId IS NULL AND activity0.removefromworklist = 0 Grab the output and send it to me. [1] https://apacheignite.readme.io/docs/sql-queries#using-h2-debug-console 2016-05-23 16:36 GMT+03:00 Alexei Scherbakov <alexey.scherbak...@gmail.com>: > Hi, > > In current state of SQL engine where is a very high probability for > necessity of query modification for efficient use with Ignite. > I'll look into your data soon. Was very busy last week. > > 2016-05-17 17:37 GMT+03:00 jan.swaelens <jan.swael...@sofico.be>: > >> Hello, >> >> Please find the attached pojo instances with group index annotations. >> JoinPerfPojos.zip >> < >> http://apache-ignite-users.70518.x6.nabble.com/file/n4993/JoinPerfPojos.zip >> > >> >> Yes I understand that there are probably better ways to retrieve the data, >> but the point of my exercise is to see how we can slide in an in memory >> solution without actually impacting the implementation of the business >> logic >> as coded today. >> >> Thanks for your insights! >> >> >> >> -- >> View this message in context: >> http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p4993.html >> Sent from the Apache Ignite Users mailing list archive at Nabble.com. >> > > > > -- > > Best regards, > Alexei Scherbakov > -- Best regards, Alexei Scherbakov
