1

We have an application running on Classic ASP which I'd like to get to use connection pooling.

The application currently uses this as its connection string:

"Provider=MSDASQL; Driver={SQL Server}; Server=db.example.com; Database=DBName; UID=Username; PWD=Password; ConnectionTimeout=15; CommandTimeout=120;"

When I bring up the ODBC connection pooling perfmon I don't see any connections in the pool.

I've read this article which suggests setting a value in HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\W3SVC\ASP\Parameters but there's no \ASP key so I'm not sure if it applies to IIS 6.0.

Does anyone have experience with this issue? Is there a simple way to toggle pooling on?

Zorlack
  • 395
  • 1
  • 5
  • 13

2 Answers2

2

The reference article (referenced at the bottom of the article you cited) states and on IIS 4.0 and later connection pooling defaults to on. So you don't need to enable it through the registry.

From another article:

To use connection pooling optimally, there are a couple of rules to live by. First, open the connection, do the work, and then close the connection. It's okay to open and close the connection multiple times on each request if you have to (optimally you apply Tip 1) rather than keeping the connection open and passing it around through different methods. Second, use the same connection string (and the same thread identity if you're using integrated authentication). If you don't use the same connection string, for example customizing the connection string based on the logged-in user, you won't get the same optimization value provided by connection pooling. And if you use integrated authentication while impersonating a large set of users, your pooling will also be much less effective.

Making sure to close the connection and using the same connection string again is the key to connection pooling. IIS will cache the connection and reuse it (essentially).

Jack B Nimble
  • 1,505
  • 1
  • 10
  • 13
  • I've been doing a bit more research and I think the problem may just be that the perfmon isn't displaying accurate data. Gotta look into this next. Thanks! – Zorlack Jun 25 '10 at 20:04
1

In order to get the counters up and working for ODBC connection pooling you must enable performance monitor in the ODBC Control panel :

  • Go to Control panel
  • Administrative tools
  • Data sources (ODBC)
  • Connection Pooling Tab
  • Enable "Performance monitor"
Sébastien Nussbaumer
  • 133
  • 2
  • 7