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.
