Re: ADO - Bad when it is time to Optimize


<cvsudheer@xxxxxxxxx> wrote in message
news:1173485633.772897.172380@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Oops..my last reply got lost.
I was testing by posting an empty reply and that came up..!
So trying again..

Thanks a lot Bob for the reply.

This KB:
http://support.microsoft.com/default.aspx?scid=kb;en-us;328476
tells the settings to be done after ADO pooling is turned off, but it
does not tell hot to turn it off.

My requirement is not to turn it off either.
We have a situation where SQL server gets slow in processing requests
due to some contention issues and at that point I can see the User
Connections shooting up. I think the reason for that is ADO Pool goes
on creating new connections when it finds no free connection in the
pool. If there were a way to set the max pool size, I could have
avoided over-stressing the SQL server which is already in a bad state.

I appreciate your help and will be happy to hear some thing about
connection pool throttling.

Thanks!
SK


[Warning! A totally unsatisfactory reply follows... <g>]

The classic example of why Connection Pooling is desirable goes something
like this...
For 0 to 9999
Set cn = As New ADODB.Connection
cn.Open ...
cn.Execute(sSQL)
... DoSomethingToTakeOffSomeClicks
cn.Close
Next
You should never have more than two or three connections. If you do then it
is likely your client workstations/applications are too fast and the server
is too slow. ie, they are making requests faster than connnections can be
re-pooled. It is a simple law of physics.

Attempting to play with the connection pool ain't gonna buy you anything.
Even if you could establish a 'maximum' all you would do is set up
application wide, random blocks (60sec delays) on cn.Open. Which is far more
likely to introduce new problems than solve any existing ones.

You have three choices (well actually only one if the first two fail to
help).

1) Rework, refactor, re-architect your applications to avoid 'connection
hogging'. Are you opening and closing all connection immediately before and
after need? Are you avoiding Command and Recordset re-use, thus keeping
active connections open? Are you using disconnected recordsets for lengthy
massages? etc. Are you testing your apps on a loaded system?
[There are many articles on the web on how to optimize ADO.]

2) You mentioned using COM/ATL - perhaps you can effectively place many of
these services in Transactions and add a 'gatekeeper' tier to some requests.
(Deferred queries). ie, build your own queue. Does everything have to be
right now?

3) If you have cleaned up everything you can, and it still doesn't work -
then upgrade the server. It is an unfortunate fact of life that sometimes a
wheelbarrow, no matter how inexpensive or simple to operate, just isn't the
right tool for the job - sometimes you need a dump truck.

hth
-ralph


.

Relevant Pages

  • Re: SQL Statement Class Helper request
    ... SQL Server will do this as well for regular queries issued ... > connection (there is also the notion of "preparing" a query for use multiple ... One advantage of using Java for the connection pool - this doesn't seem ... > I agree with you that there are ways you might want to filter the query. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Close and Dispose argument
    ... > fact that even Microsoft is not clear on the issue. ... > connections from the pool is still a debate. ... > method removes the conneciton from the connection pool". ... > Your issue was forwarded to us here at Microsoft Courseware Support. ...
    (microsoft.public.dotnet.framework.adonet)
  • Close and Dispose argument
    ... here is an email conversation between me and Microsoft ... connections from the pool is still a debate. ... Programming with Microsoft ADO.NET" module 2, p 18: "Calling the Dispose ... method removes the conneciton from the connection pool". ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Connection pool count too high?
    ... > just the connection string that determines if a new pool is needed. ... > (app domain) gets its own pool, but within that scope each transaction gets ... >> Our web application is all asp.net, using ADO.NET and SQL Server 2000 STD. ...
    (microsoft.public.dotnet.framework.adonet)
  • Timeout - max pool size reached
    ... I got the following message on my Sql Server 2000: ... The timeout period elapsed prior to obtaining a connection ... in use and max pool size was reached. ...
    (microsoft.public.dotnet.framework.adonet)