Okey, progress!
Executing this one from back in the thread:
/EXPLAIN ANALYZE SELECT DISTINCT * FROM "Activity".activity activity0
LEFT OUTER JOIN "Activity".activityuseraccountrole activityuseraccountrole0
ON activity0.activity_Id=activityuseraccountrole0.activity_Id/
Generates the following, now indicating its using indexes.
/SELECT DISTINCT
ACTIVITY0._KEY,
ACTIVITY0._VAL,
ACTIVITY0.ACTIVITY_ID,
...
FROM "Activity".ACTIVITY ACTIVITY0
/* "Activity".ACTIVITY.__SCAN_ */
/* scanCount: 121824 */
LEFT OUTER JOIN "Activity".ACTIVITYUSERACCOUNTROLE ACTIVITYUSERACCOUNTROLE0
/* "Activity"."Activityuseraccountrole_idx": ACTIVITY_ID =
ACTIVITY0.ACTIVITY_ID */
ON ACTIVITY0.ACTIVITY_ID = ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID
/* scanCount: 1149662 *//
The one with the IN statement runs fine as well:
/EXPLAIN ANALYZE SELECT DISTINCT * FROM "Activity".activity activity0
LEFT OUTER JOIN "Activity".activityuseraccountrole activityuseraccountrole0
ON activityuseraccountrole0.activity_Id = activity0.activity_Id
AND activityuseraccountrole0.useraccountrole_Id IN (1, 3)
/
/SELECT DISTINCT
ACTIVITY0._KEY,
ACTIVITY0._VAL,
ACTIVITY0.ACTIVITY_ID,
ACTIVITY0.TIMESTAMP,
...
FROM "Activity".ACTIVITY ACTIVITY0
/* "Activity".ACTIVITY.__SCAN_ */
/* scanCount: 121824 */
LEFT OUTER JOIN "Activity".ACTIVITYUSERACCOUNTROLE ACTIVITYUSERACCOUNTROLE0
/* "Activity"."Activityuseraccountrole_idx": ACTIVITY_ID =
ACTIVITY0.ACTIVITY_ID
AND USERACCOUNTROLE_ID IN(1, 3)
*/
ON (ACTIVITYUSERACCOUNTROLE0.USERACCOUNTROLE_ID IN(1, 3))
AND (ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID = ACTIVITY0.ACTIVITY_ID)
/* scanCount: 1149662 *//
Proceeding to the next case, executing this one:
/EXPLAIN ANALYZE SELECT DISTINCT * FROM "Activity".activity activity0
LEFT OUTER JOIN "Activity".activityuseraccountrole activityuseraccountrole0
ON activityuseraccountrole0.activity_Id = activity0.activity_Id
AND activityuseraccountrole0.useraccountrole_Id IN (1, 3)
LEFT OUTER JOIN "Activity".activityhistory activityhistory0
ON activityhistory0.activityhistory_Id = activity0.lastactivity_Id
AND activityhistory0.activitystate_Enumid NOT IN (37, 30, 463, 33, 464)
/
Also works like a charm now:
/SELECT DISTINCT
ACTIVITY0._KEY,
ACTIVITY0._VAL,
ACTIVITY0.ACTIVITY_ID,
...
FROM "Activity".ACTIVITY ACTIVITY0
/* "Activity".ACTIVITY.__SCAN_ */
/* scanCount: 121824 */
LEFT OUTER JOIN "Activity".ACTIVITYUSERACCOUNTROLE ACTIVITYUSERACCOUNTROLE0
/* "Activity"."Activityuseraccountrole_idx": ACTIVITY_ID =
ACTIVITY0.ACTIVITY_ID
AND USERACCOUNTROLE_ID IN(1, 3)
*/
ON (ACTIVITYUSERACCOUNTROLE0.USERACCOUNTROLE_ID IN(1, 3))
AND (ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID = ACTIVITY0.ACTIVITY_ID)
/* scanCount: 1149662 */
LEFT OUTER JOIN "Activity".ACTIVITYHISTORY ACTIVITYHISTORY0
/* "Activity".PK_ACTIVITYHISTORY: ACTIVITYHISTORY_ID =
ACTIVITY0.LASTACTIVITY_ID */
ON (NOT (ACTIVITYHISTORY0.ACTIVITYSTATE_ENUMID IN(37, 30, 463, 33,
464)))
AND (ACTIVITYHISTORY0.ACTIVITYHISTORY_ID = ACTIVITY0.LASTACTIVITY_ID)
/* scanCount: 289076 *//
Finally going back to the original SQL:
/EXPLAIN ANALYZE SELECT DISTINCT * FROM "Activity".activity activity0
LEFT OUTER JOIN "Activity".activityuseraccountrole activityuseraccountrole0
ON activityuseraccountrole0.activity_Id = activity0.activity_Id
AND activityuseraccountrole0.useraccountrole_Id IN (1, 3)
LEFT OUTER JOIN "Activity".activityhistory activityhistory0
ON activityhistory0.activityhistory_Id = activity0.lastactivity_Id
AND activityhistory0.activitystate_Enumid NOT IN (37, 30, 463, 33, 464)
LEFT OUTER JOIN "Activity".activityhistoryuseraccount
activityhistoryuseraccount0
ON activityhistoryuseraccount0.activityHistory_Id =
activityhistory0.activityhistory_Id
WHERE activity0.kernel_Id IS NULL
AND activity0.realization_Id IS NULL
AND activity0.removefromworklist = 0/
Also makes it without effort:
/SELECT DISTINCT
ACTIVITY0._KEY,
ACTIVITY0._VAL,
ACTIVITY0.ACTIVITY_ID,
...
FROM "Activity".ACTIVITY ACTIVITY0
/* "Activity"."Activity_cond_idx": REMOVEFROMWORKLIST = 0
AND KERNEL_ID IS NULL
AND REALIZATION_ID IS NULL
*/
/* WHERE (ACTIVITY0.REALIZATION_ID IS NULL)
AND ((ACTIVITY0.REMOVEFROMWORKLIST = 0)
AND (ACTIVITY0.KERNEL_ID IS NULL))
*/
/* scanCount: 120437 */
LEFT OUTER JOIN "Activity".ACTIVITYUSERACCOUNTROLE ACTIVITYUSERACCOUNTROLE0
/* "Activity"."Activityuseraccountrole_idx": ACTIVITY_ID =
ACTIVITY0.ACTIVITY_ID
AND USERACCOUNTROLE_ID IN(1, 3)
*/
ON (ACTIVITYUSERACCOUNTROLE0.USERACCOUNTROLE_ID IN(1, 3))
AND (ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID = ACTIVITY0.ACTIVITY_ID)
/* scanCount: 1147117 */
LEFT OUTER JOIN "Activity".ACTIVITYHISTORY ACTIVITYHISTORY0
/* "Activity".PK_ACTIVITYHISTORY: ACTIVITYHISTORY_ID =
ACTIVITY0.LASTACTIVITY_ID */
ON (NOT (ACTIVITYHISTORY0.ACTIVITYSTATE_ENUMID IN(37, 30, 463, 33,
464)))
AND (ACTIVITYHISTORY0.ACTIVITYHISTORY_ID = ACTIVITY0.LASTACTIVITY_ID)
/* scanCount: 286092 */
LEFT OUTER JOIN "Activity".ACTIVITYHISTORYUSERACCOUNT
ACTIVITYHISTORYUSERACCOUNT0
/* "Activity"."Activityhistoryuseraccount_idx": ACTIVITYHISTORY_ID =
ACTIVITYHISTORY0.ACTIVITYHISTORY_ID */
ON ACTIVITYHISTORYUSERACCOUNT0.ACTIVITYHISTORY_ID =
ACTIVITYHISTORY0.ACTIVITYHISTORY_ID
/* scanCount: 193274 */
WHERE (ACTIVITY0.REMOVEFROMWORKLIST = 0)
AND ((ACTIVITY0.KERNEL_ID IS NULL)
AND (ACTIVITY0.REALIZATION_ID IS NULL))/
Doing the same via my java code test I created earlier we get these timings:
executeQuery: 4224ms
rs.next: 1961ms
Would you say that these timings are in line with what you would expect? Are
there other opportunities to improve the speed of this?
Regarding the annotations vs cache config approach, I am using the cache
config code since it was auto generated and I am basically still learning
about how to use the product. Thanks for making it clear that 1 approach
should be selected, I will probably keep using the cache config approach
since we will needing some dynamic logic going forward which I can then code
in there.
And finally, a very big thank you for supporting us in this adventure! It is
rare that people take this much effort so thanks a lot!
--
View this message in context:
http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5298.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
