William (Bill) Vaughn's Musings

In response to a newsgroup question where an ASP developer (who had seen the light) wanted to know if it was still necessary to immediately close connections or if it was okay to keep them open.
________________________________
Unless your server is supporting many hundreds of users or you're under a licensing restriction, you can easily get away with creating and holding connections--lots of them. The disadvantage to this approach is that it ties up some resources on the server to maintain your connection state--but so does the connection pool approach. The advantage is that you can build and maintain state on the server. This state can consist of server-side temp tables, specific SET configurations and server-side cursors. These can't be leveraged in an ASP environment (which is probably why they were not brought forward with ADO.NET). For example, you can build a #temp table that contains user-specific rowsets, index it and use it in subsequent JOIN statements to (dramatically) improve performance. In the past (before connection pooling) we used this approach quite effecitvely and simply timed out the connections after N idle minutes.