Form Global SQLConnection

[jonbatts]

In all of the web applications I write I make many calls to a SQL database, often in one postback. When I make these calls in multiple functions, they always start the same way

Private Function MyFunction() As Boolean
    Dim sSSQL As String = ""
    Dim sqlConn As SqlConnection
    Dim sqlCmd As SqlCommand
    Dim sqlRdr As SqlDataReader

    Try
...
        sqlConn = New SqlConnection
        sqlConn.ConnectionString = Session("ConnectionString")
        sqlConn.Open()
...
    Catch ex As Exception
        Throw ex
    Finally
        If Not sqlRdr Is Nothing Then sqlRdr.Close()
        If Not sqlConn Is Nothing Then sqlConn.Close()
    End Try
End Function

In forms where I'm calling multiple functions that do this, I'm wondering if I should have a SqlConnection that's global to the entire page so I'm not opening and closing so many SqlConnections. I would of course put it in my declarations, and then instantiate it in Page_Load. What are the pros and cons of this method over the method I'm currently using? Thanks, and have a great day.

 

[onpnt]

You should always open and close the connection as soon as you are done with it and never leave it open per process. To leave a connection open while waiting for a postback is bad form and could cause problems.





_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[BrianB]

If you are into pain, you could create a static collection of connections, keep track of which are in use, and serve them up/shut them down as appropriate.

We use that technique in a couple of places and it really boosts performance, at the cost of a migraine or two.

Brian Begy
BugSentry - Automatic error reporting

http://www.bugsentry.com

 

[vbkris]

>>If you are into pain, you could create a static collection of connections, keep track of which are in use, and serve them up/shut them down as appropriate.

hi, i am really interested in this. how did this boost your performace?

cause from what I have read, an open connection is one of the most expensive objects...

Known is handfull, Unknown is worldfull

 

[BrianB]

Well, we create a static class that contains ConnecttionPool class. The ConnectionPool has a collection of connection wrappers, and a thread for examining them. The wrapper keeps track of whether or not it is busy and when it was last used.

(Side note: I find ADO.NET needlessly complicated, so we use wrapper classes that make, say, executing a sproc and returning a dataset a 1 line operation instead of the twelve (?) that ADO.NET textbooks use.)

When you ask the connection pool for a connection, it locks the list, flips through until it finds an idle connection, and returns it. If it doesn't find an idle one, it can open a new one (up until we get to our MaxConnectons variable) and failing that, throws an error. There's some built in waiting code to prevent a momentary spike from clobbering us.

Our examiner thread loops the connections and shuts down any that stale, as defined by a MaxConnectionKeepOpen variable. It also calls a meaningless select statement on each one to ensure the db connection is viable. It can shut down any that fail the test.

The connection pool object contains shutdown logic for when the asp.net process itself gets recycled, so we can cleanly rollback any open transactions (shouldn't happen) and close neatly.

When you need a connection, you ask the pool for one and it is yours until you explicitly release it. Obviously, this requires some measure of good coding practice.

Our web controls raise an event when they need a db connection and raise another when they are through with the connection.

The process was a real pain to develop, but now is just a standard part of high volume projects. The results are pretty dramatic, since we shave 2-3 seconds off of each page.

Make sense?

Brian Begy
BugSentry - Automatic error reporting

http://www.bugsentry.com

 

[jonbatts]

I'm not sure you understood my question onpnt. I'm talking about having a connection global only to the current page. It would be opened in the Page_Load and closed in the Page_Unload. Not "open while waiting for a postback"..

 

[BrianB]

You could always create it on the webform, instantiate it on load and clobber it after render.

Brian Begy
BugSentry - Automatic error reporting

http://www.bugsentry.com

 

[vbkris]

hi brian,

read through your explanation, looks like you have built a pool on default SQL Connection pool.

hmm, interesting idea...

Known is handfull, Unknown is worldfull

 

[Tarwn]

This thread is interesting to me as well, as we have made several attempts at differant types of connection pooling.

We found that in one case, using a static set of connections actually caused us a great deal of problems, occasionally returning a resultset to a differant page then the one it had been requested from.

Also, keep in mind there is internal connection pooling. Unless you turn it off, closing your connection does not actually close it (On SQL Server 2000 at least). If you don't believe me, check out the active connections after a page is loaded, you will see one from .Net still. They appear to be applicaiton level, opened when the first page is called then placed into a wait state for future calls, until IIS is restarted.

In an attempt to really close a connection and make them page based, rather than application level, I once found a method to force the creation of a new connection for each page. This promptly overloaded SQL Server because the old connections were not removed and after a certain number of pageloads SQL Server had too many open connections and stopped allowing new ones.

I definately like BrianB's solution, but I have to wonder whether it has been looked at form the SQL Server side and the connections are truly new connections. From what I have seen, I would surmise that any time you create a connection you are really connecting to a handle in the IIS connection pool, which then connects to the database. I would be curious to see if he has managed to get around the issue with the IIS connection pooling funnelling all of the "connections" into a single database connection (sp_who?).

 

[BrianB]

We actually *didn't* use default pooling because I wanted lots more control over how it worked.

We call our solution "connection pooling for the anally retentive."

Brian Begy
BugSentry - Automatic error reporting

http://www.bugsentry.com