A Note About Performance

A Note About Performance

Performance is tricky. Tweaking an application to get it running under heavy load is an expensive and time-consuming task. From the standpoint of data access, however, it is possible to ensure a high level of performance as long as you follow these two coding conventions:

• Open connections as late as possible.

• Close them as soon as possible.

These two guidelines seem simple, yet their importance cannot be overstated. Connections to databases represent real physical resources on your machine, and hogging those resources will inevitably result in poor performance. For performance-sensitive applications, it is absolutely essential that you explicitly close all of your database connections as soon as you are done with them. This is necessary for two reasons. First, the connection will not automatically be closed for you as soon as the reference goes out of scope. Garbage collection is an indeterministic process, and it is impossible to guarantee the timing of a collection (unless you force it, which is a horribly expensive solution). Second, if a connection is left to be collected, it might not make it back into the connection pool, requiring the creation of a new connection. This adds more overhead to an application. Ideally, you want to get a connection from the connection pool (by calling Open on your connection object) and then return it to the pool immediately when you are done with it (by calling Close).

On another performance note, you should make sure that you choose your managed data provider carefully. Although the OleDb providers enable you to access virtually any database or data source (including SQL), Microsoft has also developed a SQL-specific managed provider that does not use OLE DB. In fact, the SqlClient namespace provides significant performance gains when accessing SQL Server based databases because it is a network-level provider and communicates directly with a SQL server through low-level network access. The OleDb managed provider uses the OLE DB database providers, not only introducing an additional layer for OLE DB but also running it through the COM interop layer, further increasing overhead. When performance is critical, use only the OleDb provider for data sources other than Microsoft SQL Server (unless other companies have produced their own managed database providers by the time you read this).

Using Open and Close Effectively

If you need to do a lot of database work (such as making multiple sequential queries), it is important to make sure that you do not hold on to your database connections throughout the series of calls. If possible, you should release the connections between queries. This does add some additional overhead to your method (because it has to get connections from and return them to the connection pool more frequently), but it increases churn in the pool and ensures that connections will always be available to handle your application s needs. Take it from us when your application runs out of connections, its performance craps out big time (yes, that is a technical term), and that s putting it mildly. The following example illustrates good database etiquette:

Function DBTest()    Dim conn As New SqlConnection(connStr)    Dim cmd As New SqlCommand("Select * From Customers", conn)    ' Open the connection and execute the command    conn.Open()    Dim dr As SqlDataReader = cmd.ExecuteReader()    While dr.Read()       ' Do some processing    End While    ' Close the reader and the connection    dr.Close()    conn.Close()    ' Get ready to do your next query    cmd.CommandText = "Select * From Employees"    ' Open the connection and execute the next command    conn.Open()    dr = cmd.ExecuteReader()    While dr.Read()       ' Do some other processing    End While    ' Close the reader and the connection    dr.Close()    conn.Close() End Function