Hello, 2013/10/8 Noel Grandin <[email protected]>
> Hi > > Thanks for the updated test case. > > I've looked into this, and the situation is thusly: > > We have special case optimised code for handling "IN (1,2,3)" queries. > > Unfortunately, for "IN (?,?,?)" queries the optimised code does not get > used because we at the point in time we run the optimiser, the optimiser > sees the prepared-statement-parameters and bails out. > > Running the optimiser on a PreparedStatement kind of defeats the point of > a PreparedStatement, which is to minimise per-execution setup cost. > That's certainly true. I'm not sure if H2 does this, but other databases keep cursors and execution plans around for re-execution, in case equivalent statements are encountered. However, given the length of the IN predicate, I think that the above rule might no longer hold true. The SQL string is different for 3895 parameters than it is for 3896 parameters. This is a common problem when tuning IN predicates. When they are long, it is almost always better to inline the bind values (not only in H2). Of course, it would be even better to create an actual semi-join using a subselect. > It might be possible to teach the code in > org.h2.expression.ConditionIn#**optimize > to handle this case, but I'm loathe to add that much complexity for corner > cases like this. > I'll think about it some more. > I couldn't help but think about this a bit myself as well. ;-) In SQL, some expressions are "deterministic" (in the Oracle sense of the word), and some expressions are constant. Both expression attributes propagate through expressions. If a function F is deterministic, then "F(A, B)" is deterministic only if both A and B are also deterministic expressions. This can be further expanded to constants. If F is deterministic, then "F(A, B)" is constant only if A and B are constant expressions. Bind values are constant expressions for a single query execution. Thus, it would be possible to pre-process a SQL statement, substituting all constant expressions by their constant value. E.g. CAST(? AS BIGINT) could be substituted by the actual converted bind value. Alternatively, such expressions could be pre-calculated once and cached for a single query execution, similarly to what Oracle does with scalar subquery caching ( http://blog.jooq.org/2011/09/02/oracle-scalar-subquery-caching/) I would imagine that this is quite valuable in many non-corner cases as well, as H2 has a lot of built-in deterministic functions, such as: LOWER(), UPPER(), etc. -- 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.
