Hi Thomas, I don't think it's slow because of a missing index. If I use the inline statement its fast. Did you tried the example?
Am Montag, 7. Oktober 2013 21:35:16 UTC+2 schrieb Thomas Mueller: > > 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 > <[email protected]<javascript:> > > 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 [email protected] <javascript:>. >> To post to this group, send email to [email protected]<javascript:> >> . >> 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.
