ADO connection pooling and pepared statements

[moosedeja]

I have an ASP (IIS 4.0) based website fronting a SQL Server 2000
database. I am trying to avoid using dynamic SQL for queries, and I am
considering prepared statements as an alternative...

All the advice I have seen strongly suggests that connection objects
should not be explicitly cached, either at the application or session
level. Therefore, I am creating a connection for each page (same
connection string), and closing it immediately after use. By my
understanding, this is utilising the MTS connection pool and is the
most resource efficient method to use.

However, I have seen a number of comments to the effect that connection
pooling and prepared statements do not mix.

For example...

"Avoid creating prepared statements or temporary stored procedures.
These create server resources attached to a specific connection.
Because you might not get the same connection from the pool the next
time you ask for one, you lose the benefit of creating them in the
first place. Worse, because the connection might reside in the pool for
a long time, it can get clogged up with the useless but expensive
server resources created by each "user" that creates them." - Rob
Macdonald
(http://msdn.microsoft.com/library/de...0/html/Rob.asp)

Is this still the case? This article was written in 2000, and I was
hoping there was now some workaround for this. Are there any SQL
Server/driver level settings that can fix or alleviate this problem.
Are there any other options besides prepared statements or stored
procedures? Thanks.

[andy]

mo*******@hotma il.com wrote:

I have an ASP (IIS 4.0) based website fronting a SQL Server 2000
database. I am trying to avoid using dynamic SQL for queries, and I am considering prepared statements as an alternative...

All the advice I have seen strongly suggests that connection objects
should not be explicitly cached, either at the application or session
level. Therefore, I am creating a connection for each page (same
connection string), and closing it immediately after use. By my
understanding, this is utilising the MTS connection pool and is the
most resource efficient method to use.
Yes.
That's the way everyone I know does it.

However, I have seen a number of comments to the effect that connection pooling and prepared statements do not mix.

I don't think I've ever seen prepared statements used in an app.

I reckon you should instead be considering stored procedures.
Won't you still be running the risk of code injection if you go for
prepared statements?

[Erland Sommarskog]

(mo*******@hotm ail.com) writes:

I have an ASP (IIS 4.0) based website fronting a SQL Server 2000
database. I am trying to avoid using dynamic SQL for queries, and I am
considering prepared statements as an alternative...

All the advice I have seen strongly suggests that connection objects
should not be explicitly cached, either at the application or session
level. Therefore, I am creating a connection for each page (same
connection string), and closing it immediately after use. By my
understanding, this is utilising the MTS connection pool and is the
most resource efficient method to use.

However, I have seen a number of comments to the effect that connection
pooling and prepared statements do not mix.

I think there is some confusion here. What you really want is parameterized
queries:

SELECT col1, col2 FROM tbl WHERE thiscol = ? AND thatcol = ?

You can sett the .Prepared property to true, but that is not really the
main thing with leaving completely dynamically built SQL statements
behind. The above query will get cached in the procedure cache of
SQL Server, so any connection can reuse it.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp