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 >>> <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...@googlegroups.com. >>>> To post to this group, send email to h2-da...@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.
