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:45 PM, Markus Fengler <[email protected]>wrote: > Hi, > > I hava a query with a lot of Parameters. If I use it with inlined > Paramters it works. But if I use a prepared Statement the execute does not > come to an end and the cpu goes to 100%. > > Note: for the sql-generation I use the jooq library. I also posted it as > issue (https://github.com/jOOQ/jOOQ/issues/2768) but it is h2database > specific. > > Here is an extract of the example. You can download the full example code > at http://jmlp.volans.uberspace.de/ftp/issueExample.zip as maven project: > > @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 [email protected]. > To post to this group, send email to [email protected]. > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
