Hi,
According to a simple test case, H2 is already using an index when using an
unnecessary CAST. So I'm not sure yet what the problem is for your case.
Could you run the query with "explain" and post the query plan? Example:
Connection conn = DriverManager.getConnection("jdbc:h2:mem:");
Statement stat = conn.createStatement();
stat.execute("create table test(id identity)");
PreparedStatement prep;
ResultSet rs;
prep = conn.prepareStatement(
"explain select * from test where id in (?, ?)");
prep.setInt(1, 1);
prep.setInt(2, 1);
rs = prep.executeQuery();
rs.next();
System.out.println(rs.getString(1));
prep = conn.prepareStatement(
"explain select * from test where id in " +
"(cast(? as bigint), cast(? as bigint))");
prep.setInt(1, 1);
prep.setInt(2, 1);
rs = prep.executeQuery();
rs.next();
System.out.println(rs.getString(1));
conn.close();
The output is:
SELECT
TEST.ID
FROM PUBLIC.TEST
/* PUBLIC.PRIMARY_KEY_2: ID IN(?1, ?2) */
WHERE ID IN(?1, ?2)
SELECT
TEST.ID
FROM PUBLIC.TEST
/* PUBLIC.PRIMARY_KEY_2: ID IN(CAST(?1 AS BIGINT), SIN(CAST(?2 AS
BIGINT))) */
WHERE ID IN(CAST(?1 AS BIGINT), SIN(CAST(?2 AS BIGINT)))
So in both cases the primary key is used. When changing "id in" to "-id
in", then a table scan is used (no index). An index is also used when using
a deterministic function or a combination (SIN(?), SIN(CAST(? AS INT)), and
so on).
Regards,
Thomas
On Tuesday, October 8, 2013, Lukas Eder wrote:
>
>
> 2013/10/8 Thomas Mueller <[email protected] <javascript:_e({},
> 'cvml', '[email protected]');>>
>
>> Hi,
>>
>> Would it be possible to disable generating CAST? If the bind variable is
>> set using PreparedStatement.setLong, I don't see why CAST would be needed.
>>
>
> This is why:
> https://groups.google.com/forum/#!msg/h2-database/byINJvfm1S8/gtHaq1jvyPMJ
>
> After I had reported the above, I had written this blog post here,
> explaining why jOOQ casts bind values if databases don't infer them in some
> cases:
> http://blog.jooq.org/2011/08/31/rdbms-bind-variable-casting-madness
>
> Remember? :-)
> But I'll review those casts. I'm sure that many of them aren't needed, and
> jOOQ could omit blindly adding casts to all bind variables, e.g. when
> placed in the IN predicate.
>
>
>> Of course support for superfluous CAST (or any deterministic function on
>> a parameter) could be implemented in H2, but so far it wasn't needed, and
>> I'm hesitant to implement features that are not really needed. Also, it
>> might break a known workaround to force not using a certain index (using
>> the expression "-ID=-?"). I know that's a hack :-)
>>
>
> True, I'm aware of similar hacks with Oracle: ID + 0 = ?. Although, that's
> not exactly what I was proposing. While -ID prevents index usage, -? is
> still a constant. So "ID = -(-?)" should still work the same way as "ID =
> ?", in principle.
>
>
>> > I don't think it's slow because of a missing index.
>>
>> OK, so the index is not be missing, but it is probably not used because
>> of the CAST function. That would explain the difference.
>>
>> In general, I would probably use a profiler to check what's going on. See
>> also http://h2database.com/html/performance.html#built_in_profiler -
>> also, it would help to know the query plans, see
>> http://h2database.com/html/performance.html#explain_plan
>>
>>
>> Regards,
>> Thomas
>>
>> --
>> You received this message because you are subscribed to a topic in the
>> Google Groups "H2 Database" group.
>> To unsubscribe from this topic, visit
>> https://groups.google.com/d/topic/h2-database/1B4PuijeaYU/unsubscribe.
>> To unsubscribe from this group and all its topics, send an email to
>> [email protected] <javascript:_e({}, 'cvml',
>> 'h2-database%[email protected]');>.
>> To post to this group, send email to
>> [email protected]<javascript:_e({}, 'cvml',
>> '[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] <javascript:_e({},
> 'cvml', 'h2-database%[email protected]');>.
> To post to this group, send email to
> [email protected]<javascript:_e({}, 'cvml',
> '[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.
