FYI Database Connection Performance

[AndrewdAzotus]

This post is as a consequence of the following quote from jmcilhinney

DO NOT create your connection object separately to your command object. You should be create your connection object where you create your command. There is no benefit to creating a single connection object in a module. ADO.NET is designed specifically to NOT be used like that. Create the connection and the command together, open the connection, execute the command, close the connection. That is the proper way to use ADO.NET.

So I began thinking that it must be more efficient (performance wise) to open the connection to the database and just leave it open, so I wrote this:

        Dim TableRead As SqlDataReader ' OleDbDataReader        Dim Cxn As New SqlConnection ' OleDbConnection()
        Dim CxnOpen As Boolean = True
        Dim Lp1 As Integer
        Dim StartedAt As DateTime
        Dim t1 As TimeSpan
        Dim idx As Integer
        Dim Descr As String
        Dim MasterAssetList As New Dictionary(Of Integer, String)



        StartedAt = Now()
        For Lp1 = 1 To 10000
            ' Cxn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source=C:\[Database]\Data.mdb")
            Cxn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=""C:\[Database]\Data.mdf"";Integrated Security=True;Connect Timeout=30;User Instance=True"


            ' Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;
            Try
                Cxn.Open()
            Catch ex As Exception
                MessageBox.Show("Failed to connect to data source")
                CxnOpen = False
            End Try


            If CxnOpen Then
                MasterAssetList.Clear()
                ' TableRead = New OleDbCommand("Select * from AcsTbl", Cxn).ExecuteReader
                TableRead = New SqlCommand("Select * from SQLTbl", Cxn).ExecuteReader
                While TableRead.Read
                    idx = TableRead("Idx")
                    Descr = TableRead("NameGiven")
                    MasterAssetList.Add(idx, Descr)
                    MasterAssetList.Add(100 + idx, Descr)    ' [#]
                    MasterAssetList.Add(200 + idx, Descr)    ' [#]
                    MasterAssetList.Add(300 + idx, Descr)    ' [#]
                End While
                TableRead.Close()
            End If


            Cxn.Close()
        Next
        t1 = Now - StartedAt
        Msgbox("It Took " & t1)

and I ran it five times modifying the code a little each time and got the following results.

Database Access	Time
Access (mdb) file opening and closing connection inside the loop	7m29s
Access (mdb) file opening and closing connection outside the loop	0m15s
SQL Server opening and closing connection inside the loop	0m10s
SQL Server opening and closing connection outside the loop	0m04.06s
SQL Server opening connection in settings.vb and closing connection in MyApplication_Shutdown	0m03.55s

I admit that the last is, perhaps, a little odd but I wanted to see the timings, then I thought I would post them here just for interest.

The three lines with [#] comments at the end were inserted since the SQL table had fifteen rows and the Access table had 64 rows so I was trying to even the code out a little.

It's not a case of can you run them faster since I am not going to publish any details of the system I'm running.

But the results make an interesting comparison...

 

[jmcilhinney]

Noone would ever recommend opening and closing connections in a loop. What you are supposed to do with ADO.NET is open and close a connection each time you want to perform data access. If you have a loop that accesses the database 10,000 times then that would be considered 1 data access task to be performed. What you are not supposed to do is just open a connection is case you might need to use it some time and keep it open when you know for a fact that you're not using it.

 

[AndrewdAzotus]

I wasn't recommending opening and closing a database connection in a loop as shown here.

The original point of this exercise was to time creating a database connection and closing a database connection and to ask the question did it make sense to keep opening a database connection each and every time I wanted to access a database or (from a performance point of view) to open the connection just once for the application. It was your comment in another post somewhere that sparked this train of thought.

It made sense to do something once the database connection was open, hence the arbitrary filling of a list box.

The whole point of putting it in a loop to 10,000 times is to get some kind of sensible timing. Back in the 80's when I first started computing a single open and close was enough to give a sensible time that wasn't in milliseconds.

This exercise then extended to seeing the difference between SQL Server and an Access 2k3 file using Jet 4.0. I do not have Access 2k7 nor Jet 12 (never liked the ribbons so stuck with 2k3)

You cannot honestly believe I was actually suggesting that a db cxn should be opened and closed within a loop like that.

It was purely an exercise to find out the timings of an open and close.

If an application were (in it's normal course of operation) to have 1,000 separate (i.e. not in a loop but in different forms and subs and functions etc.) database accesses using SQL Server as a database source then if the connection was opened at the application load and then closed at application termination then compared to opened the connection around each of the database reads then the additional execution time would be only an additional 0.4 seconds - not something to worry about from a user's point of view

You can see that the difference in timings for access was a little greater - 7.25 minutes for 10,000 or about three quarters of a minute for 1,000 opens and closes. And I do not think that 1,000 completely separate opens and closes for a heavy weight real world application is unreasonable.

As I have said, this was a timing EXERCISE and I was not suggesting anyone should actually code like this in a real life application.

You comment about putting a open and close in a 10,000 loop is wrong and it would be considered one database operation is true. but then, filling a list box on a form 10,000 times in a loop with exactly the same data is as pointless as opening and closing a database connection 10,000 times within a loop too.

 

[jmcilhinney]

You can see that the difference in timings for access was a little greater - 7.25 minutes for 10,000 or about three quarters of a minute for 1,000 opens and closes. And I do not think that 1,000 completely separate opens and closes for a heavy weight real world application is unreasonable.

I think that you have misinterpreted my statement somewhat. If you had, for instance, a console application that did some heavy database work then it may well be legitimate to open a connection once, do everything and then close it at the end. In such a scenario it may be legitimate to consider the entire thing one database task. What I was referring to was what is the more usual case around here, i.e. a Windows application where the user retrieves some data and works with it for a while and then saves it. If you need to open and close a database connection 1000 times in an application like that then the user will have been working with the application for such a long time that even an extra 45 seconds will not be significant. In a Windows application, most of the time is spent waiting around for the user to do something. That time should generally not be spent with a database connection open.

 

[Herman]

In any case, the server side connection timeout would be bound to cause issues for any extended period of time. As said above, you should open a connection only when you need it and close it as soon as you are finished. There is no real world application where you would need to throw 10000 queries in a loop to a DB, that would just be poor design. You should be able to construct a working SQL script or stored procedure for this that you would only need to run once.

 

[JohnH]

There is Connection Pooling (ADO.NET), that is where actual reuse of database connections occurs.

 

[AndrewdAzotus]

I think that you have misinterpreted my statement somewhat.

Uh, no, I do not think I misinterpreted what you actually wrote at all.

If you had, for instance, a console application that did some heavy database work then it may well be legitimate to open a connection once, do everything and then close it at the end. In such a scenario it may be legitimate to consider the entire thing one database task. What I was referring to was what is the more usual case around here, i.e. a Windows application where the user retrieves some data and works with it for a while and then saves it. If you need to open and close a database connection 1000 times in an application like that then the user will have been working with the application for such a long time that even an extra 45 seconds will not be significant. In a Windows application, most of the time is spent waiting around for the user to do something. That time should generally not be spent with a database connection open.

Well, of course Windows spends most of its time waiting for the user. What I find with Windows is that it isn't very good at it as it sometimes doesn't notice that the user is asking for its attention and/or forgets to go back to respond to the user as it is too busy doing something else...

 

[AndrewdAzotus]

In any case, the server side connection timeout would be bound to cause issues for any extended period of time. As said above, you should open a connection only when you need it and close it as soon as you are finished. There is no real world application where you would need to throw 10000 queries in a loop to a DB, that would just be poor design. You should be able to construct a working SQL script or stored procedure for this that you would only need to run once.

No, there isn't a real world exercise where you would loop something 10,000 times. just like there is no real world application where someone would need to run 26 miles or...

It was an exercise...