It looks kind of strange to me. Nevermind, lets return to the initial question. Currently we are comparing performance of H2 engine with Oracle engine. In general performance should be the on the same level, H2 even may perform slightly worse because of the additional Ignite's overhead. You should not expect some wondrous performance boost just from running SQL query on single Ignite's server. Ignite really shines when used in distributed manner, splitting a lot of data over several nodes.
2016-06-01 12:05 GMT+03:00 jan.swaelens <[email protected]>: > 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. > -- Best regards, Alexei Scherbakov
