connection pooling--when is pool disposed?

[jcaulder]

I have read some different comments/posts on when a connection pool is disposed and was hoping for some clarification(.NET 1.1).

The connection string does not specifically set ANY properties related to pooling. I open a connection, then dispose of this connection, does the pool still exist? Some posts say that if no open connection exists in the pool, it is disposed. Others appear to say it persists throughout the life of the application.

My application behaves as if pools are not being utilized so I am wondering if my pools are being removed.

TIA!

 

[jcaulder]

Reading up on this topic at the Microsoft website it appears the following is true:

1)The pool will persist even if ALL connections are closed by the application.
2)If Min Pool Size is either not specified in the connection string or is specified as zero, the connections in the pool will be closed after a period of inactivity.

The application that prompted by question is a massive batch processing application that runs thousands of queries.

#1 above specifically answers my question. Closing the connection in the application simply returns that connection as available in the pool. Even if all connections are closed by the application, the pool and its connections remain available. So closing connections timely within the application is critical so they can be reused within the pool. Reviewing the code carefully I found some leaks where connections(datareaders) were not closed eventually hitting the default max pool size leading to timeout errors and poor performance.

#2 while I'm not sure what the inactivity time period is, I think given the nature of my application I should set the min pool size to something like 5 or 10 so that connection objects are readily available early. Because my application is a batch process that runs daily, completes and then closes, I want enough connections available at any given time so that the pool does not have to keep adding connection objects. Also, I think setting the incremental size in sets of 10 may be beneficial. Basically, this batch process needs to be given the necessary connections so it can quickly perform its tasks and then close. Because the life of the application is relatively short but its interaction with the database is significant, it appears providing it the connections it needs is the correct approach.

The way the application is currently written, there are many places where there are nests of loops of datareaders, each one of these using a connection. I did not write the application initially but rather inherited it. One thing I am doing is modifying the loops such that the datareaders are used to load collections(or other objects) then immediately closed. Then use the collection objects as the controls for the loops. This should greatly reduce the time any given connection is open and ultimately lead to much fewer connections being utilized and/or needed within the pool.

Hopefully this will help someone else with a similar problem. Please let me know if anything seems amiss in my understanding of pooling or if there are other tweaks I could make to increase performance.

Thanks!

J