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.
