Hi, I guess the query is slow for some reason, possibly because there is no index. Did you already check the documentation at http://h2database.com/html/performance.html#explain_plan ?
Regards, Thomas On Sun, Oct 6, 2013 at 10:27 PM, Markus Fengler <nar...@googlemail.com>wrote: > Hi, > > I want to get a ResultSet and the query contains a lot of Parameters. > If I use an inline Query then it works but if I use a PreparedStatement > with indexed Parameters there seems to be a problem. > Here is an example for this strange behaviour. > > You can download the whole Example as maven project at > http://jmlp.volans.uberspace.de/ftp/issueExample.zip > > Note: For generating the sql I use jooq library. I also posted the issue > at their repository but it seems that it is h2 specific. ( > https://github.com/jOOQ/jOOQ/issues/2768) > > @Test > public void testBugExample() throws SQLException { > /* Preparation of Data */ > DSLContext dsl = DSL.using(this.con, SQLDialect.H2); > List<Long> activeFolders = > dsl.select(FILES.ID).from(FILES).where(FILES.TYP.eq("D"), > FILES.AVAILABLE.isTrue()).fetch(FILES.ID); > List<Long> activeFiles = > dsl.select(FILES.ID).from(FILES).where(FILES.TYP.eq("F"), > FILES.AVAILABLE.isTrue()).fetch(FILES.ID); > > /* the Query with the Bug */ > SelectConditionStep<Record1<Long>> qry = dsl.select(FILES.ID > ).from(FILES).join(FOLDERMAP).on(FILES.ID.eq(FOLDERMAP.ID)) > .where(FILES.ID.notIn(activeFiles), FOLDERMAP.PARENT.in > (activeFolders)); > > String qryIndexed = qry.getSQL(ParamType.INDEXED); > String qryInline = qry.getSQL(ParamType.INLINED); > > /* InlineParameter work */ > log.info("Query Inline: " + qryInline); > { > Statement stm = con.createStatement(); > ResultSet rs = stm.executeQuery(qryInline); > while (rs.next()) > rs.getLong(1); > rs.close(); > stm.close(); > } > > /* InlineParameter work */ > log.info("Query Indexed: " + qryIndexed); > { > PreparedStatement stm = con.prepareStatement(qryIndexed); > > /* set Parameter */ > int pos = 1; > for (Long e : activeFiles) > stm.setLong(pos++, e); > for (Long e : activeFolders) > stm.setLong(pos++, e); > > ResultSet rs = stm.executeQuery(); > while (rs.next()) > rs.getLong(1); > rs.close(); > stm.close(); > } > > /* does not finish to this line */ > log.info("finish"); > } > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to h2-database+unsubscr...@googlegroups.com. > To post to this group, send email to h2-database@googlegroups.com. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/groups/opt_out. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
