Hi Suresh, Good thought, I actually did exactly what you said: 1000 -> 1.8 sec compile 2000: 6.8 sec 4000: 28.3 sec.
All plan generate tuple list, and not unpack nodes. Suspecting some recursive call, non linear scalable linked with tuple list handling, and associated stack overflow common problems with recursive calls? Anu found core dumps associated with 10000 runs. And these are happening from trafci or sqlci… the above query is not generating nested join plan because my table d is empty still. I am hoping it will do NJ as soon as I have some real data and updated stats… I wish I could force the plan manually as generated by: select x.a, d.a from (values (?[10000],?[10000],?[10000],?[10000])) as x(a,b,c,d) join directories d on x.b = d.b and x.c= d.c and x.d = d.d; beautifully generating unpack node … Eric From: Suresh Subbiah [mailto:suresh.subbia...@gmail.com] Sent: Tuesday, January 17, 2017 7:17 PM To: user@trafodion.incubator.apache.org Subject: Re: question on using Batch for a select statement with jdbc Hi Eric, I don't there is a generic CQD here. If there was a core file and we analyzed it we might be able to find something specific to this situation. Can we please try with say 1000 values and 4000 parameters? Thanks Suresh On Tue, Jan 17, 2017 at 4:53 PM, Eric Owhadi <eric.owh...@esgyn.com<mailto:eric.owh...@esgyn.com>> wrote: Trying this workaround but hitting a timeout exception at prepare time: final static String missingVal1 = "select x.a, d.a from (values "; final static String missingValVar = "(?,?,?,?)"; final static String missingIVal2 = ") as x(a,b,c,d) join d on x.b = d.b and x.c = d.c and x.d = d.d where x.a is not null"; StringBuilder missingVals = new StringBuilder(101000); missingVals.append(missingVal1); missingVals.append(missingValVar); for(int i=1; i< rowsetSize; i++){ missingVals.append(',').append(missingValVar); } missingVals.append(missingVal2); PreparedStatement missingValsstsmt= conn2.prepareStatement(missingVals.toString()); Exception in thread "main" org.trafodion.jdbc.t4.TrafT4Exception: Server aborted abnormally or Connection timed out at org.trafodion.jdbc.t4.TrafT4Messages.createSQLException(TrafT4Messages.java:284) at org.trafodion.jdbc.t4.InputOutput.doIO(InputOutput.java:376) at org.trafodion.jdbc.t4.T4Connection.getReadBuffer(T4Connection.java:157) at org.trafodion.jdbc.t4.T4Statement.getReadBuffer(T4Statement.java:196) at org.trafodion.jdbc.t4.T4Statement.Prepare(T4Statement.java:129) at org.trafodion.jdbc.t4.InterfaceStatement.prepare(InterfaceStatement.java:1126) at org.trafodion.jdbc.t4.TrafT4PreparedStatement.prepare(TrafT4PreparedStatement.java:2209) at org.trafodion.jdbc.t4.TrafT4Connection.prepareStatement(TrafT4Connection.java:775) at DirectoriesHelper.main(DirectoriesHelper.java:45) Am I doing something crazy? Or is there a CQD/param that would help prepare that statement containing 10 000 (?,?,?,?) , so 40 000 parameters in it? Eric From: Eric Owhadi Sent: Tuesday, January 17, 2017 2:47 PM To: user@trafodion.incubator.apache.org<mailto:user@trafodion.incubator.apache.org> Subject: RE: question on using Batch for a select statement with jdbc Hi Selva I came across this to and believe that setArray is to support SQL Array type that we don’t support anyway. Eric From: Selva Govindarajan [mailto:selva.govindara...@esgyn.com] Sent: Tuesday, January 17, 2017 2:46 PM To: user@trafodion.incubator.apache.org<mailto:user@trafodion.incubator.apache.org> Subject: Re: question on using Batch for a select statement with jdbc Hi Eric, Looking at the JDBC specification, I am guessing preparedStatement.setArray and using Array interface can do the trick. But, setArray is unsupported feature in Trafodion jdbc drivers. Selva ________________________________ From: Eric Owhadi <eric.owh...@esgyn.com<mailto:eric.owh...@esgyn.com>> Sent: Tuesday, January 17, 2017 12:08 PM To: user@trafodion.incubator.apache.org<mailto:user@trafodion.incubator.apache.org> Subject: question on using Batch for a select statement with jdbc Hi Trafodioneers, Have following jdbc question: select x.a, d.a from (values (?,?,?,?)) as x(a,b,c,d) join directories d on x.b = d.b and x.c= d.c and x.d = d.d; I was thinking of using Batch to fill the list of values, but then I struggle with how to invoke the query. executeBatch does not return a resultSet, as I guess it is used for upsert or inserts? Can I use executeQuery(), and that will do the trick as long as I use addBacth()? Or it is not possible to use addBatch for this use model? Thanks in advance for the help, Eric
