Re: Unexpected performance issue with SQL query followed by error

[jan.swaelens]

Hello,

This one has been running for 10 minutes now without producing results - so
rather the join.

/EXPLAIN ANALYZE SELECT DISTINCT * FROM "Activity".activity activity0
LEFT OUTER JOIN "Activity".activityuseraccountrole activityuseraccountrole0
ON activity0.activity_Id=activityuseraccountrole0.activity_Id/

This one works fine though:

/EXPLAIN ANALYZE SELECT DISTINCT * FROM "Activity".activity activity0,
"Activity".activityuseraccountrole activityuseraccountrole0
WHERE activity0.activity_Id=activityuseraccountrole0.activity_Id/

/SELECT DISTINCT
    ACTIVITY0._KEY,
    ACTIVITY0._VAL,
    ACTIVITY0.ACTIVITY_ID,
    ACTIVITY0.TIMESTAMP,
    ACTIVITY0.CONTAINER_ID,
    ACTIVITY0.ACTIVITYTYPE_ID,
    ACTIVITY0.REALIZATION_ID,
    ACTIVITY0.KERNEL_ID,
    ACTIVITY0.PREDECESSORTYPE_ENUMID,
    ACTIVITY0.SUCCESSORTYPE_ENUMID,
    ACTIVITY0.DURATIONUNIT_ENUMID,
    ACTIVITY0.NAME,
    ACTIVITY0.NAME_MLID,
    ACTIVITY0.DESCRIPTION,
    ACTIVITY0.DESCRIPTION_MLID,
    ACTIVITY0.DURATION,
    ACTIVITY0.REQUIRED,
    ACTIVITY0.ESTIMSTARTDATE,
    ACTIVITY0.ESTIMSTARTHOUR,
    ACTIVITY0.ESTIMENDHOUR,
    ACTIVITY0.ESTIMENDDATE,
    ACTIVITY0.REMOVEFROMWORKLIST,
    ACTIVITY0.SEQUENCENR,
    ACTIVITY0.SESSION_ID,
    ACTIVITY0.LASTACTIVITY_ID,
    ACTIVITY0.SYSREPOPERATION_ID,
    ACTIVITY0.LIFECYCLEREPORTING,
    ACTIVITY0.DUEDATE,
    ACTIVITY0.PRIORITY_ENUMID,
    ACTIVITY0.NOTIFY,
    ACTIVITYUSERACCOUNTROLE0._KEY,
    ACTIVITYUSERACCOUNTROLE0._VAL,
    ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID,
    ACTIVITYUSERACCOUNTROLE0.USERACCOUNTROLE_ID
FROM "Activity".ACTIVITYUSERACCOUNTROLE ACTIVITYUSERACCOUNTROLE0
    /* "Activity".ACTIVITYUSERACCOUNTROLE.__SCAN_ */
    /* scanCount: 1027840 */
INNER JOIN "Activity".ACTIVITY ACTIVITY0
    /* "Activity".PK_ACTIVITY: ACTIVITY_ID =
ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID */
    ON 1=1
    /* scanCount: 2055678 */
WHERE ACTIVITY0.ACTIVITY_ID = ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID/

So looks like the LEFT OUTER is the culprit, or at least one of them.

br
jan



--
View this message in context: 
http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5267.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.