Prepared statement slower than string concatenated query

Normally when you use JDBC it’s clever to use prepared query statement (with parameter substitution) to not have SQL plan calculation done all the time. Not so with CRDB it seems.

I’ve written a query that gets data from some linked up tables with some subqueries because joins were horribly slow. However, in DBeaver the query is executed within some milliseconds (around 30 or so) which is sufficient for my purpose.

However, executing the very same query with java postgresql driver using hibernate with statement parameters was running about 5 seconds!

After rewriting the query with string concatenation it executes within around 20-40ms again.

I’d call this an unexpected behavior. Can anyone explain that to me? Is it a bug?

Thank you sahlex for sharing this concern with us.

The behavior you describe certainly looks anomalous. We would love to have a deeper look into it, in case indeed there is a bug. If possible, could you open an issue on Github and provide some more details about how you execute the query? You can refer to this forum thread in your issue.

A possible explanation is the following, which I am taking from my experience with the Go (not Java) drivers. When placeholders are used, the driver opts for separate prepare and execute phases. In this execution mode, each query will be processed using 2 or 3 roundtrips to the database (one prepare order, then a bind order to associate values to the placeholders, then one execute order). When there are no placeholders, a single execute message is sent to the database.

The choice between these two paths is made automatically by the driver depending on whether placeholders are present. This alone explains a large performance difference, at least in a Go program. Is it possible that your Java code switches between one or the other execution path in a similar way?

Hi Raphael.

Thanks for coming back to me. I opened up an issue https://github.com/cockroachdb/cockroach/issues/21463