Hi Lukas, you are right. Now I did four tests.
First with inline (e.g. where foldermap.parent in (1,2,3,4,...)) - the query takes less than 2 seconds to finish. Second with indexed parameters (e.g. where foldermap.parent in (?,?,?,?,...)) - the query takes arount 37 seconds to finish. Third with indexed and cast parameters (e.g. where foldermap.parent in (cast(? as bigint),cast(? as bigint),cast(? as bigint),cast(? as bigint),...)) - the query takes about 160 seconds to finish. And last but not least a fourth with inline and cast (e.g. where foldermap.parent in (cast(1 as bigint), cast(2 as bigint),...) - the query takes less than one second. For me it looks like that there is a performance issue at the h2 in handling indexed parameters. Am Dienstag, 8. Oktober 2013 08:31:11 UTC+2 schrieb Lukas Eder: > > I'll tune in here to provide you with some background info from the jOOQ > side. When Markus used jOOQ to *inline* bind values (e.g. WHERE > foldermap.parent IN (1, 2, 3, 4)), the query ran in an acceptable time on > my machine - around 1/2s. In the latter query, jOOQ renders bind values as > question marks for later binding. What may be interesting to note is the > fact that for historic reasons, jOOQ generates CAST(? AS BIGINT) for bind > values. I'm not sure if the CAST() is the source of confusion here. But > when creating thread dumps, I noticed that a significant amount of time is > spent in Value.convertTo(): > > "main" prio=6 tid=0x0000000001d2e000 nid=0x1798 runnable [0x0000000001d0d000] > java.lang.Thread.State: RUNNABLE > at org.h2.value.Value.convertTo(Value.java:507) > at org.h2.expression.Function.getSimpleValue(Function.java:781) > at org.h2.expression.Function.getValueWithArgs(Function.java:992) > at org.h2.expression.Function.getValue(Function.java:466) > at org.h2.expression.ConditionIn.getValue(ConditionIn.java:52) > at org.h2.expression.ConditionNot.getValue(ConditionNot.java:33) > at org.h2.expression.ConditionAndOr.getValue(ConditionAndOr.java:94) > at org.h2.expression.ConditionAndOr.getValue(ConditionAndOr.java:94) > at org.h2.expression.Expression.getBooleanValue(Expression.java:180) > at org.h2.command.dml.Select.queryFlat(Select.java:520) > > So, without having evaluated this further, this *looks* as though a lot > of work is performed for the complete IN and NOT IN predicate for each > record. > > Hope this helps > Lukas > > Am Montag, 7. Oktober 2013 21:38:44 UTC+2 schrieb Markus Fengler: >> >> 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]>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]. >>>> 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.
