ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "jan.swaelens" <jan.swael...@sofico.be>
Subject Re: Unexpected performance issue with SQL query followed by error
Date Wed, 01 Jun 2016 09:05:36 GMT
Hello,

Both are using the same database and same data, I did some count(*) checks
on the tables in Oracle and H2 and they yield the same count. This means
that things go wrong on another level, not sure where though.

Lets take a step back and rerun the tests:

This is the SQL:

/SELECT DISTINCT activity0.activity_id, activity0.sequencenr,
activity0.name_mlid, activity0.name, activity0.description_mlid,
activity0.description, activity0.durationunit_enumid, activity0.duration,
activity0.required, activity0.predecessortype_enumid,
activity0.successortype_enumid, activity0.removefromworklist,
activity0.lastactivity_id, activity0.lifecyclereporting, activity0.duedate,
activity0.priority_enumid, activity0.notify, activity0.timestamp,
activity0.ActivityType_id, activity0.container_id, activity0.realization_id,
activity0.kernel_id, activity0.sysrepoperation_id 
FROM activity activity0 LEFT OUTER JOIN activityhistory activityhistory0 ON
activityhistory0.activityhistory_id = activity0.lastactivity_id 
LEFT OUTER JOIN activityuseraccountrole activityuseraccountrole0 ON
activityuseraccountrole0.activity_id = activity0.activity_id 
LEFT OUTER JOIN activityhistoryuseraccount activityhistoryuseraccount0 ON
activityhistoryuseraccount0.ActivityHistory_id =
activityhistory0.activityhistory_id  
WHERE activity0.kernel_id IS NULL 
AND activity0.realization_id IS NULL 
AND NOT activityhistory0.activitystate_enumid IN (37, 30, 463, 33, 464)  
AND ((activityuseraccountrole0.useraccountrole_id IN (1, 3)  
AND (activity0.removefromworklist = 0  
OR activityhistoryuseraccount0.UserAccount_id IS NULL)) 
OR activityhistoryuseraccount0.UserAccount_id = 600301) /

Explain via SQL Developer:
explainplan_new.png
<http://apache-ignite-users.70518.x6.nabble.com/file/n5355/explainplan_new.png>  

SQL Via H2:

/EXPLAIN ANALYZE  SELECT DISTINCT activity0.activity_id,
activity0.sequencenr, activity0.name_mlid, activity0.name,
activity0.description_mlid, activity0.description,
activity0.durationunit_enumid, activity0.duration, activity0.required,
activity0.predecessortype_enumid, activity0.successortype_enumid,
activity0.removefromworklist, activity0.lastactivity_id,
activity0.lifecyclereporting, activity0.duedate, activity0.priority_enumid,
activity0.notify, activity0.timestamp, activity0.ActivityType_id,
activity0.container_id, activity0.realization_id, activity0.kernel_id,
activity0.sysrepoperation_id 
FROM "Activity".activity activity0 LEFT OUTER JOIN
"Activity".activityhistory activityhistory0 ON
activityhistory0.activityhistory_id = activity0.lastactivity_id 
LEFT OUTER JOIN "Activity".activityuseraccountrole activityuseraccountrole0
ON activityuseraccountrole0.activity_id = activity0.activity_id 
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 NOT activityhistory0.activitystate_enumid IN (37, 30, 463, 33, 464)  
AND ((activityuseraccountrole0.useraccountrole_id IN (1, 3)  
AND (activity0.removefromworklist = 0  
OR activityhistoryuseraccount0.UserAccount_id IS NULL)) 
OR activityhistoryuseraccount0.UserAccount_id = 600301) /

Explain via H2:

/SELECT DISTINCT
    ACTIVITY0.ACTIVITY_ID,
    ACTIVITY0.SEQUENCENR,
...
FROM "Activity".ACTIVITY ACTIVITY0
    /* "Activity"."Activity_cond_idx": KERNEL_ID IS NULL
        AND REALIZATION_ID IS NULL
     */
    /* WHERE (ACTIVITY0.KERNEL_ID IS NULL)
        AND (ACTIVITY0.REALIZATION_ID IS NULL)
    */
    /* scanCount: 121058 */
LEFT OUTER JOIN "Activity".ACTIVITYHISTORY ACTIVITYHISTORY0
    /* "Activity".PK_ACTIVITYHISTORY: ACTIVITYHISTORY_ID =
ACTIVITY0.LASTACTIVITY_ID */
    ON ACTIVITYHISTORY0.ACTIVITYHISTORY_ID = ACTIVITY0.LASTACTIVITY_ID
    /* scanCount: 189554 */
LEFT OUTER JOIN "Activity".ACTIVITYUSERACCOUNTROLE ACTIVITYUSERACCOUNTROLE0
    /* "Activity"."Activityuseraccountrole_idx": ACTIVITY_ID =
ACTIVITY0.ACTIVITY_ID */
    ON ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID = ACTIVITY0.ACTIVITY_ID
    /* scanCount: 1148898 */
LEFT OUTER JOIN "Activity".ACTIVITYHISTORYUSERACCOUNT
ACTIVITYHISTORYUSERACCOUNT0
    /* "Activity"."Activityhistoryuseraccount_idx": ACTIVITYHISTORY_ID =
ACTIVITYHISTORY0.ACTIVITYHISTORY_ID */
    ON ACTIVITYHISTORYUSERACCOUNT0.ACTIVITYHISTORY_ID =
ACTIVITYHISTORY0.ACTIVITYHISTORY_ID
    /* scanCount: 1060391 */
WHERE ((NOT (ACTIVITYHISTORY0.ACTIVITYSTATE_ENUMID IN(37, 30, 463, 33,
464)))
    AND ((ACTIVITY0.KERNEL_ID IS NULL)
    AND (ACTIVITY0.REALIZATION_ID IS NULL)))
    AND ((ACTIVITYHISTORYUSERACCOUNT0.USERACCOUNT_ID = 600301)
    OR ((ACTIVITYUSERACCOUNTROLE0.USERACCOUNTROLE_ID IN(1, 3))
    AND ((ACTIVITY0.REMOVEFROMWORKLIST = 0)
    OR (ACTIVITYHISTORYUSERACCOUNT0.USERACCOUNT_ID IS NULL))))/

Hmm so yeah looks like we are seeing strange numbers in the explain, even
though the starting data and queries are the same.






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

Mime
View raw message