Question about JDBC prepared statements in H2

[Silvio]

Apart from SQL-injection considerations Is there any advantage or disadvantage in using PreparedStatement parameters versus literal values when the statement will only be executed once?

Our generic database handling code currently uses statement parameters for all situations and column types. We are considering changing this to literal values in the SQL string for numerical types, timestamp types etc. and only use parameters for string values when the statement will be executed only once (which we know upfront).

[Noel Grandin]

I prefer PreparedStatement purely so I can avoid thinking about escaping string values.

But if that doesn't worry you, then there is no down side to using Statement for execute only once.

[Silvio]

Thanks Noel. String values are exactly what we where planning to keep as parameters.

So in many cases one or more parameters will remain (for string values) but most will be replaced by literals since in our case by far most column types are numeric, timestamp or UUID.

I was hoping for perhaps some speedup if the query planner could make a more optimal query plan based on known values in the SQL string as opposed to a more generic query plan that would have to take all possible values for one or more parameters into account. If that would never be the case in H2 then there is little to no advantage for us in changing anything.

[Noel Grandin]

On 11/7/2022 4:59 PM, Silvio wrote:
>
> I was hoping for perhaps some speedup if the query planner could make a more optimal query plan based on known values in
> the SQL string as opposed to a more generic query plan that would have to take all possible values for one or more
> parameters into account. If that would never be the case in H2 then there is little to no advantage for us in changing
> anything.
>

There are very few opportunities to do this (that I am aware of), so I don't expect it to make any real difference.

But if you do find any potential optimisations that we are missing, please feel free to log a feature request :-)