Is it possible to use Command.Prepare with connection pooling?

[Andy]

I've been thinking about using command.Prepare() to prepare the execution of
some of the statements that I run repeatedly in the database. We have run
into situations where prepared statements have drastically improved
performance, especially against Oracle.

One of the issues with calling Command.Prepare is that the prepared
statement is then affinitized to the Connection that it was prepared on,
correct?

If that is true, how can it be possible to use Command.Prepare and also to
release connections back to the pool immediately as per ADO.Net best
practices? How could I possibly use the command again as there is no way to
associate the Command with the actual connection which has been released
back to the pool?

Is it then true that there is no way to use prepared statements and
connection pooling together in an application?

I would appreciate any insight from anyone else who has tried this. It looks
to me like the only way this can happen is to write your own connection
pool.

Regards,

Andrew Elmhorst

 

[Frans Bouma [C# MVP]]

I've been thinking about using command.Prepare() to prepare the execution of
some of the statements that I run repeatedly in the database. We have run
into situations where prepared statements have drastically improved
performance, especially against Oracle.

One of the issues with calling Command.Prepare is that the prepared
statement is then affinitized to the Connection that it was prepared on,
correct?

What prepare does is ensuring an execution plan of a query in compiled
form is preserved. This is thus only possible for 100% if the connection
the query was first executed (and thus prepared) is kept open and reused
for the subsequent calls.

If that is true, how can it be possible to use Command.Prepare and also to
release connections back to the pool immediately as per ADO.Net best
practices? How could I possibly use the command again as there is no way to
associate the Command with the actual connection which has been released
back to the pool?

That's not the purpose of Prepare. Prepare is useful if you have to
execute 50 inserts to the same table for example, in the same routine.

Is it then true that there is no way to use prepared statements and
connection pooling together in an application?

I would appreciate any insight from anyone else who has tried this. It looks
to me like the only way this can happen is to write your own connection
pool.

Or better schedule your statements so they are executed in the same
routine which allows you to keep a connection open

Frans

--

 

[Andy]

Or better schedule your statements so they are executed in the same

routine which allows you to keep a connection open

Frans

So the pattern of keeping prepared statements around and re-using them is
pretty much non-existant? Back in the old days when it was more common to
hold on to a single connection in an application it was very common to see
certain prepared statements held on to that were used frequently throughout
the lifetime of an application.

What's going to happen now is that eventually each connection is going to
end up having the statement prepared on it. However, there will be no way to
gain access to it. The server will have the prepared statements sitting
there, waiting to be called on each connection in the pool, but there would
be no way to subsequently call them. It seems kind of wasteful to me.

Calling Command.Prepare is a somewhat expensive operation that saves time
(and resources) when the command is later re-used. I'm beginning to suspect
that it is the design of the connection pool that is suspect. There should
be a way to prepare a statement in such a way that it will automatically be
prepared on any connection that it is called on. If it has already been
prepared on the connection, the existing prepared statement is used. I would
be happy if the framework just made me call Prepare once and then allowed me
to re-use the same command on any connection and automatically prepare it. I
would be equally happy if the connection pool just managed it all for me.

If there were just some easy way to retrieve the connection id ( spid in SQL
Server ) from a connection, I could implement this myself. Unfortunately,
those low-level details are kept hidden away. As I said previously, I think
the only way around this is to build your own connection pool. And, speaking
from experience, I know that is not an easy thing to do well.

Andrew Elmhorst
http://sifbits.blogspot.com/

 

[Frans Bouma [C# MVP]]

So the pattern of keeping prepared statements around and re-using them is
pretty much non-existant? Back in the old days when it was more common to
hold on to a single connection in an application it was very common to see
certain prepared statements held on to that were used frequently throughout
the lifetime of an application.

That is because in those old days, compiled statements were not cached.
On SqlServer 7 / 2000 for example a parameterized query is compiled and
the execution plan is cached (same as with procs). This means that a
prepare doesn't bring you anything: any subsequent calls of the command
(doesn't matter which connection) will re-use the execution plan.

What's going to happen now is that eventually each connection is going to
end up having the statement prepared on it. However, there will be no way to
gain access to it. The server will have the prepared statements sitting
there, waiting to be called on each connection in the pool, but there would
be no way to subsequently call them. It seems kind of wasteful to me.

it is my understanding prepare compiles the dyn. query on the server,
however modern RDBMS-s cache compiled queries already.

Calling Command.Prepare is a somewhat expensive operation that saves time
(and resources) when the command is later re-used.

I fail to see in what way, as I can't find any evidence prepare is
required to get better performance, unless the RDBMS doesn't cache
execution plans for dynamic queries. (which can be the case in your
situation)

I'm beginning to suspect
that it is the design of the connection pool that is suspect. There should
be a way to prepare a statement in such a way that it will automatically be
prepared on any connection that it is called on. If it has already been
prepared on the connection, the existing prepared statement is used. I would
be happy if the framework just made me call Prepare once and then allowed me
to re-use the same command on any connection and automatically prepare it. I
would be equally happy if the connection pool just managed it all for me.

I don't think it's the pool, I think it's the RDBMS, which tosses away
any resources once a connection is closed/reset, thus also the execution
plan of the query.

Frans

--

 

[Andy]

That is because in those old days, compiled statements were not cached. On
SqlServer 7 / 2000 for example a parameterized query is compiled and the
execution plan is cached (same as with procs). This means that a prepare
doesn't bring you anything: any subsequent calls of the command (doesn't
matter which connection) will re-use the execution plan.

I wouldn't necessarily agree that Preparing Commands does nothing on SQL
Server. It does end up sending a more efficient copy over the wire ( It
calls sp_execute instead of sp_executesql and only sends the parameter
values ). However in most cases that is only going to provide a marginal
boost, depending on the size of the query and network pipe to the server.
It's just something to keep in mind if someone does have a slow pipe.

I fail to see in what way, as I can't find any evidence prepare is
required to get better performance, unless the RDBMS doesn't cache
execution plans for dynamic queries. (which can be the case in your
situation)

Now as I said previously our main problem is Oracle, which definitely does
have huge performance gains using prepared statements. ( without them it
runs out of memory for our application ). Our application can connect to a
wide variety of databases and we would like to use the same pattern for all.
Obviously this is a feature of databases that was implemented to increase
performance on some platforms, but hasn't been integrated real well into the
ADO.Net framework.

I don't think it's the pool, I think it's the RDBMS, which tosses away any
resources once a connection is closed/reset, thus also the execution plan
of the query.

True, I just think that the pool ( or whatever part of the framework makes
the most sense ) should have been designed in such a way to allow prepared
statements to be re-used after connections have been released back to the
pool. Just some simple way to prepare a statement on a connection if it
hasn't been prepared yet on that connection.
My guess is that when all is said and done ( e.g. we get an Oracle customer
that requests the .Net version of our app ) we're going to be writing our
own connection pool.

Andy