The query still has a potential for performance improvement. Could you provide me with the execution plan for query produced by Oracle database ?
About the union query I think we have slightly different models, so this is not an issue. 2016-05-30 15:32 GMT+03:00 jan.swaelens <[email protected]>: > Well iterating trough via the JDBC resultset takes a good 2 seconds, query > itself just over 4 seconds. This is actually slower than running it via the > oracle jdbc connection (executeQuery: 645ms / rs iteration: 1271ms). Looks > like my hopes to cut down on the response time of this particular use case > where a little too optimistic. > > Trying the union query I get cryptic 'Failed to parse query' errors without > giving a clue about the actual cause, narrowing it down to the 1st chunk of > the batch it gives me this: > > / ERROR [20160530 14:32:14] - Failed to execute local query: > GridQueryRequest [reqId=1, pageSize=1024, space=Activity, > qrys=[GridCacheSqlQuery [qry=SELECT > (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".ACTIVITYUSERACCOUNTROLE > ACTIVITYUSERACCOUNTROLE0 > ON ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID = ACTIVITY0.ACTIVITY_ID > LEFT OUTER JOIN "Activity".ACTIVITYHISTORY ACTIVITYHISTORY0 > ON (NOT (ACTIVITYHISTORY0.ACTIVITYSTATE_ENUMID IN(37, 30, 463, 33, 464))) > AND (ACTIVITYHISTORY0.ACTIVITYHISTORY_ID = ACTIVITY0.LASTACTIVITY_ID) > LEFT OUTER JOIN "Activity".ACTIVITYHISTORYUSERACCOUNT > ACTIVITYHISTORYUSERACCOUNT0 > ON (ACTIVITYUSERACCOUNTROLE0.USERACCOUNTROLE_ID IN(1, 3)) AND > (ACTIVITYHISTORYUSERACCOUNT0.ACTIVITYHISTORY_ID = > ACTIVITYHISTORY0.ACTIVITYHISTORY_ID) > WHERE (ACTIVITY0.REMOVEFROMWORKLIST = 0) AND ((ACTIVITY0.KERNEL_ID IS NULL) > AND (ACTIVITY0.REALIZATION_ID IS NULL))) __C0 > FROM SYSTEM_RANGE(1, 1), params=[], paramIdxs=[], paramsSize=0, > cols={__C0=GridSqlType [type=17, scale=0, precision=2147483647, > displaySize=2147483647, sql=ARRAY]}, alias=null]], > topVer=AffinityTopologyVersion [topVer=1, minorTopVer=0], extraSpaces=null, > parts=null] > class org.apache.ignite.IgniteCheckedException: Failed to execute SQL > query. > at > > org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:832) > at > > org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQueryWithTimer(IgniteH2Indexing.java:855) > at > > org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onQueryRequest(GridMapQueryExecutor.java:454) > at > > org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onMessage(GridMapQueryExecutor.java:184) > at > > org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.send(GridReduceQueryExecutor.java:1065) > at > > org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.query(GridReduceQueryExecutor.java:572) > at > > org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing$2.iterator(IgniteH2Indexing.java:956) > at > > org.apache.ignite.internal.processors.cache.QueryCursorImpl.iterator(QueryCursorImpl.java:61) > at > > org.apache.ignite.internal.processors.cache.query.jdbc.GridCacheQueryJdbcTask$JdbcDriverJob.execute(GridCacheQueryJdbcTask.java:240) > at > > org.apache.ignite.internal.processors.job.GridJobWorker$2.call(GridJobWorker.java:509) > at > > org.apache.ignite.internal.util.IgniteUtils.wrapThreadLoader(IgniteUtils.java:6455) > at > > org.apache.ignite.internal.processors.job.GridJobWorker.execute0(GridJobWorker.java:503) > at > > org.apache.ignite.internal.processors.job.GridJobWorker.body(GridJobWorker.java:456) > at > org.apache.ignite.internal.util.worker.GridWorker.run(GridWorker.java:110) > at > > java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) > at > > java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) > at java.lang.Thread.run(Thread.java:745) > Caused by: org.h2.jdbc.JdbcSQLException: Scalar subquery contains more than > one row; SQL statement: > SELECT > (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".ACTIVITYUSERACCOUNTROLE > ACTIVITYUSERACCOUNTROLE0 > ON ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID = ACTIVITY0.ACTIVITY_ID > LEFT OUTER JOIN "Activity".ACTIVITYHISTORY ACTIVITYHISTORY0 > ON (NOT (ACTIVITYHISTORY0.ACTIVITYSTATE_ENUMID IN(37, 30, 463, 33, 464))) > AND (ACTIVITYHISTORY0.ACTIVITYHISTORY_ID = ACTIVITY0.LASTACTIVITY_ID) > LEFT OUTER JOIN "Activity".ACTIVITYHISTORYUSERACCOUNT > ACTIVITYHISTORYUSERACCOUNT0 > ON (ACTIVITYUSERACCOUNTROLE0.USERACCOUNTROLE_ID IN(1, 3)) AND > (ACTIVITYHISTORYUSERACCOUNT0.ACTIVITYHISTORY_ID = > ACTIVITYHISTORY0.ACTIVITYHISTORY_ID) > WHERE (ACTIVITY0.REMOVEFROMWORKLIST = 0) AND ((ACTIVITY0.KERNEL_ID IS NULL) > AND (ACTIVITY0.REALIZATION_ID IS NULL))) __C0 > FROM SYSTEM_RANGE(1, 1) [90053-175] > at > org.h2.message.DbException.getJdbcSQLException(DbException.java:332) > at org.h2.message.DbException.get(DbException.java:172) > at org.h2.message.DbException.get(DbException.java:149) > at org.h2.message.DbException.get(DbException.java:138) > at org.h2.expression.Subquery.getValue(Subquery.java:41) > at org.h2.expression.Alias.getValue(Alias.java:37) > at org.h2.command.dml.Select.queryFlat(Select.java:533) > at org.h2.command.dml.Select.queryWithoutCache(Select.java:632) > at org.h2.command.dml.Query.query(Query.java:297) > at org.h2.command.dml.Query.query(Query.java:284) > at org.h2.command.dml.Query.query(Query.java:36) > at org.h2.command.CommandContainer.query(CommandContainer.java:91) > at org.h2.command.Command.executeQuery(Command.java:196) > at > > org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:106) > at > > org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:829) > ... 16 more > ERROR [20160530 14:32:14] - Failed to execute job due to unexpected > runtime > exception [jobId=d0c34a10551-f256b204-cb49-4f35-8adb-7b4b575bac77, > ses=GridJobSessionImpl [ses=GridTaskSessionImpl > [taskName=o.a.i.i.processors.cache.query.jdbc.GridCacheQueryJdbcTask, > dep=GridDeployment [ts=1464611392368, depMode=SHARED, > clsLdr=weblogic.utils.classloaders.ChangeAwareClassLoader@cccf859 finder: > weblogic.utils.classloaders.CodeGenClassFinder@369a3f9c annotation: > miles@miles, clsLdrId=03b34a10551-f256b204-cb49-4f35-8adb-7b4b575bac77, > userVer=0, loc=true, > sampleClsName=o.a.i.i.processors.cache.GridCacheAdapter$LoadCacheJob, > pendingUndeploy=false, undeployed=false, usage=2], > taskClsName=o.a.i.i.processors.cache.query.jdbc.GridCacheQueryJdbcTask, > sesId=b0c34a10551-f256b204-cb49-4f35-8adb-7b4b575bac77, > startTime=1464611534110, endTime=9223372036854775807, > taskNodeId=f256b204-cb49-4f35-8adb-7b4b575bac77, > clsLdr=weblogic.utils.classloaders.ChangeAwareClassLoader@cccf859 finder: > weblogic.utils.classloaders.CodeGenClassFinder@369a3f9c annotation: > miles@miles, closed=false, cpSpi=null, failSpi=null, loadSpi=null, > usage=1, > fullSup=false, subjId=f256b204-cb49-4f35-8adb-7b4b575bac77, > mapFut=IgniteFuture [orig=GridFutureAdapter [resFlag=0, res=null, > startTime=1464611534120, endTime=0, ignoreInterrupts=false, state=INIT]]], > jobId=d0c34a10551-f256b204-cb49-4f35-8adb-7b4b575bac77]] > javax.cache.CacheException: Failed to run map query remotely. > at > > org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.query(GridReduceQueryExecutor.java:585) > at > > org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing$2.iterator(IgniteH2Indexing.java:956) > at > > org.apache.ignite.internal.processors.cache.QueryCursorImpl.iterator(QueryCursorImpl.java:61) > at > > org.apache.ignite.internal.processors.cache.query.jdbc.GridCacheQueryJdbcTask$JdbcDriverJob.execute(GridCacheQueryJdbcTask.java:240) > at > > org.apache.ignite.internal.processors.job.GridJobWorker$2.call(GridJobWorker.java:509) > at > > org.apache.ignite.internal.util.IgniteUtils.wrapThreadLoader(IgniteUtils.java:6455) > at > > org.apache.ignite.internal.processors.job.GridJobWorker.execute0(GridJobWorker.java:503) > at > > org.apache.ignite.internal.processors.job.GridJobWorker.body(GridJobWorker.java:456) > at > org.apache.ignite.internal.util.worker.GridWorker.run(GridWorker.java:110) > at > > java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) > at > > java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) > at java.lang.Thread.run(Thread.java:745) > Caused by: javax.cache.CacheException: Failed to execute map query on the > node: f256b204-cb49-4f35-8adb-7b4b575bac77, class > org.apache.ignite.IgniteCheckedException:Failed to execute SQL query. > at > > org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.fail(GridReduceQueryExecutor.java:257) > at > > org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.onFail(GridReduceQueryExecutor.java:247) > at > > org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.onMessage(GridReduceQueryExecutor.java:228) > at > > org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.sendError(GridMapQueryExecutor.java:525) > at > > org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onQueryRequest(GridMapQueryExecutor.java:501) > at > > org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onMessage(GridMapQueryExecutor.java:184) > at > > org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.send(GridReduceQueryExecutor.java:1065) > at > > org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.query(GridReduceQueryExecutor.java:572) > ... 11 more > / > > Not sure you need to spend time in this though, for me the UNION path is > not > feasible anyhow but in case you like to go deeper on these errors in terms > of the product I'll be happy to run additional tests. > > br > jan > > > > -- > View this message in context: > http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5311.html > Sent from the Apache Ignite Users mailing list archive at Nabble.com. > -- Best regards, Alexei Scherbakov
