Thread: UseServerSidePrepare default

The docs on ODBC configuration options recommend UseServerSidePrepare=1
for server versions 7.4 and above. That's a good recommendation. Given
that "7.4 and above" covers all supported versions of PostreSQL, and
every reasonable version people might want to use a new driver with, how
about we make UseServerSidePrepare=1 the default, so that people don't
need to set it manually?

- Heikki

On 19.04.2013 22:14, Heikki Linnakangas wrote:
> The docs on ODBC configuration options recommend UseServerSidePrepare=1
> for server versions 7.4 and above. That's a good recommendation. Given
> that "7.4 and above" covers all supported versions of PostreSQL, and
> every reasonable version people might want to use a new driver with, how
> about we make UseServerSidePrepare=1 the default, so that people don't
> need to set it manually?

I hear no objections, so committed. UseServerSidePrepare=1 is now the
default, for the upcoming driver version 9.3.X.

- Heikki

On 22.08.2013 13:57, Dave Cramer wrote:
> I presume this is similar to JDBC's use of server side prepare.

Yeah, it's similar. In psqlodbc, though, it controls more than just
whether to use unnamed or named plans in the server. With
UseServerSidePrepare=0, parameters are handled completely in the driver,
replacing the parameter markers with the values in the query itself.
IIRC the jdbc driver used to do that too, in older versions - psqlodbc
still does that.

(I'm planning to change that, so that parameters are always bound using
extended query protocol, but not in the 9.3 version yet).

> If so do you see a performance problem with servers older than 9.2
> since Tom's fix to planning prepared statements is only in 9.2 and
> above ?

Yeah, it's certainly possible. There's one thing that makes it less
likely to be a problem than with JDBC, though. The ODBC API doesn't
conflate prepared statements with parameterized queries like the JDBC
API does. There is a function called SQLExecDirect() which is used to
immediately execute a given SQL statement (ie. the query is passed as a
string), with given parameters. If you want to execute the statement
many times, you use a different function, SQLPrepare(), to prepare it
once and execute many times. SQLExecDirect() always uses unnamed plans,
even with UseServerSidePrepare=1.

But yeah, there may well be applications where UseServerSidePrepare=1
gives a performance hit, especially when running against < 9.2 servers.
It's still a better default IMHO, you can still turn it off if you have to.

- Heikki