Understanding ADO.NET Performance Issues and Using Best Practices

The following sections discuss some performance strategies and best practices when working with ADO.NET.

Selecting Data Providers

Many data providers are available to work with a single database, and selecting a best-suited data provider is the first step to designing performance-oriented, datadriven solutions. Some of the common data providers discussed in this book are Sql, OleDb, ODBC, OracleClient, and SQLXML.

Each data provider acts in a different way because of its internal architecture and the way it's designed. For example, the ODBC data provider uses ODBC drivers, and the OleDb data provider uses OLE DB providers to connect to different data sources. Each of these technologies works differently internally.

Selecting a data provider depends on your application's requirements. This section discusses the different scenarios and the best solutions.

To work with SQL Server databases, you can use any of these three data providers: Sql, OleDb, and ODBC. But when working with SQL Server 7.0 or later databases, the Sql data provider is the best choice. No other data provider can beat the performance of the Sql data provider in this case. The Sql data provider can provide much faster data access than the OleDb and ODBC data providers. That said, the Sql data provider isn't the best choice to work with SQL Server 6.5 or prior versions of SQL Server.

When working with Access databases, OleDb is the obvious choice. You can even use ODBC data providers, but OleDb provides better performance.

The ODBC data provider is useful when your application needs to access an ODBC data source or there aren't OleDb or other providers available. For example, if you need to access Excel or text data sources, you can simply do this using ODBC data sources.

Now let's say your application needs to access an Oracle database. There are different ways you can work with Oracle databases. You can use the ODBC data provider. Microsoft already has released an Oracle .NET data provider called OracleClient. Oracle offers another .NET provider developed for Oracle databases. Besides these three data providers, CoreLab offers an Oracle .NET data provider called OraDirect. So, now the question is this: Which one do you choose? Obviously, using an Oracle .NET data provider (either Microsoft, Oracle, or CoreLab) is better than using an ODBC data provider to access Oracle databases. Performance-wise, the Oracle data provider is faster than the ODBC data provider because there's no overhead ODBC layer.

Note

The .NET Framework 1.1 (or Visual Studio 2003) installs the Oracle and ODBC data providers. The OleDb, Sql, Odbc, and Oracle data providers are defined in the System.Data.OleDb, System.Data.SqlClient, System.Data.Odbc, and System.Data.OracleClient namespaces (respectively).

Choosing from different Oracle .NET data providers depends on your options. Microsoft's Oracle .NET data provider is available free as a part of ADO.NET. If you want to use a third-party data provider such as OraDirect, you need to pay for it.

So, what's the solution when you need to work with different databases? Developing custom data access code is definitely a good idea. A custom data provider can consume all data providers to find out the best solution. Because all data providers implement ADO.NET interfaces, you can write some generic code based on the interfaces that will work with any data provider. The following section shows you how to write a generic data access component that utilizes multiple data providers to give you the best options.

Writing a Generic Data Access Component

In this section, you'll create a simple generic data access class that allows you to select a DataAdapter at runtime. Based on the similar theory, you can extend this class by adding other common functionality to it.

Listing 22-4 lists the generic class. As you can see, this code uses interfaces to create a connection and DataAdapter objects. This class has two methods: GetConnection and GetDataAdapter. The GetConnection method returns an IDbConnection object created using different data providers based on the connection type and connection string passed in the method. The code also provides functionality for the Sql, OleDb, ODBC, and Oracle data providers.

Listing 22-4: Generic Data Access Class

 ' Generic Data Access component class Public Class GenericDataAccessComp   Private idbConn As IDbConnection = Nothing   Private idbAdapter As IDbDataAdapter = Nothing   Private dbAdapter As DbDataAdapter = Nothing   ' Default connection type exposed through   ' ConnectionType   ' 1 - Sql; 2 - OleDb, 3 - Odbc   ' 4 - Oracle   Private DefConnType As Int16 = 1   ' Default connection string exposed through   ' ConnString property   Private DefConnStr As String   Public Sub GenericDataAccessComp()   End Sub   ' GetConnection returns IDbConnection   Public Function GetConnection(ByVal connType As Integer, _   ByVal connString As String) As IDbConnection     Select Case connType       Case 1         ' OleDb Data Provider         idbConn = New SqlConnection(connString)       Case 2         ' Sql Data Provider         idbConn = New OleDbConnection(connString)       Case 3         ' ODBC Data Provider         idbConn = New OdbcConnection(connString)       Case 4         ' Oracle data provider         idbConn = New OracleConnection(connString)       Case Else         Exit Function     End Select     Return idbConn   End Function   ' GetDataAdapter returns IDbDataAdapter   Public Function GetDataAdapter(ByVal connType As Integer, _   ByVal conn As IDbConnection, ByVal sql As String) _   As IDbDataAdapter     Select Case connType       Case 1         ' OleDb Data Provider         idbAdapter = New SqlDataAdapter(sql, conn)       Case 2         ' Sql Data Provider         idbAdapter = New OleDbDataAdapter(sql, conn)       Case 3         ' ODBC Data Provider         idbAdapter = New OdbcDataAdapter(sql, conn)       Case 4         'Oracle data provider         idbAdapter = New OracleDataAdapter(sql, conn)       Case Else         Exit Function     End Select     Return idbAdapter   End Function End Class 

The GetDataAdapter method returns an IDbDataAdapter object created based on the connection and connection type variables passed in the method. You can also add methods to return a DataSet, a DataReader, and other objects from this class.

Now you're going to create a Windows application that consumes the class in Listing 22-4. Add a GroupBox control, four RadioButton controls, a DataGrid control, and a Button control. After changing the properties of these controls, the final form looks like Figure 22-11. When you select a data provider and click the Connect button, the program uses the selected data provider.

Figure 22-11: Generic data access class consumer application

First, define some variables that you use in the application (see Listing 22-5). As you can see, this code defines connection strings for different data providers.

Listing 22-5: User-Defined Variables

 ' User defined variables   Private connString As String = Nothing   Private sql As String = Nothing   Private conn As IDbConnection = Nothing   Private adapter As IDbDataAdapter = Nothing   Private OleDbConnString As String = _    "Provider=Microsoft.Jet.OLEDB.4.0;" & _    "Data Source=c:\\Northwind.mdb"   Private SqlConnString As String = _     "Integrated Security=SSPI; Initial Catalog=Northwind;" & _     "Data Source=localhost;"   Private OdbcConnString As String   Private OracleConnString As String 

Second, on the Connect button click event handler, you check what data provider is selected and based on that selection, you create a connection using the GetConnection method of the previously discussed class. You also call the GetDataAdapter method that returns a DataAdapter.

Once data is in a DataAdapter, you simply call its Fill method to fill data from the DataAdapter to a DataSet. As you can see, the code opens a connection, creates and fills a DataSet, and then binds the DataSet to the DataGrid. In this case, you have limited the functionality to Sql and OleDb data providers, but you can also specify the connection strings for Oracle and ODBC data providers. Listing 22-6 shows how to use the generic data access class.

Listing 22-6: Using the Generic Data Access Class

 Private Sub ConnectBtn_Click(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles ConnectBtn.Click     ' Construct SQL statement     sql = "SELECT * FROM Employees"     Dim daComp As GenericDataAccessComp = _     New GenericDataAccessComp     ' SQL data adapter     If SqlRadioButton.Checked Then       conn = daComp.GetConnection(1, SqlConnString)       adapter = daComp.GetDataAdapter(1, conn, sql)       ' OleDb data adapter     ElseIf OleDbRadioButton.Checked Then       conn = daComp.GetConnection(2, OleDbConnString)       adapter = daComp.GetDataAdapter(2, conn, sql)     End If     ''' Add code for other data adapters     ' Open connection. If you don't open it explicitly,     ' calling DataAdapter will open it internally     If conn.State <> ConnectionState.Open Then       conn.Open()     End If     ' Create and fill a DataSet     Dim ds As DataSet = New DataSet     adapter.Fill(ds)     ' Display DataSet in DataGrid     DataGrid1.DataSource = ds.Tables(0)     ' Close connection     If conn.State = ConnectionState.Open Then       conn.Open()     End If   End Sub End Class 

Now if you run the application and click the Connect button, you'll see data in the DataGrid.

You can extend the functionality of this generic data access component by adding various methods and their overloaded forms. You can also add functionality for other data providers including Oracle, MySql, and WinCE.

Choosing a Development Mode: Designer vs. Manual

This book covered both designer and manual approaches for adding data components to applications. The designer approach is easy to use and takes less time to write data-driven applications. (See Chapter 2 for more details.) You simply drag and drop data components to a form and follow simple steps using wizards, and the application is ready in few minutes. For example, say you want to write a Windows application that allows users to view, delete, edit, update, and navigate data through DataGrid controls and you already know the database and database tables. In this scenario, using the Data Form Wizard isn't a bad idea. However, you have no idea what happened under the hood; further, this approach is rigid, and there's no flexibility. You can go and edit code by hand, but the queries written by the designer are a big headache if you try to update them manually. On the other hand, if you write code manually, you know what code is where and how it works. Writing code manually may be time consuming, but it's worth it for experienced developers who know how and where to write code.

Alternatively, you can mix the approaches to get the best out of both worlds. You can use the designer to add some controls, and you can write some code manually.

Again, it depends on you to decide what approach to adapt. We prefer the manual approach over the designer approach because the designer writes lots of unwanted code and leaves no option to modify it. For beginners, it's better to use a designer rather than writing bad code. (But if you're already on Chapter 22 of this book, then you're not a beginner anymore!)

Retrieving Data: DataReader, XmlReader, and DataSet

One of the most frequently asked questions on discussion forums and newsgroups relates to whether you should use a DataReader or a DataSet. You can use a DataReader, XmlReader, and DataSet to retrieve data from a database. When reading hundreds of records, the DataReader is twice as fast as an XmlReader or DataSet. This difference gets bigger when there's higher user load and more records.

Now, the question is this: Which one do you use? Obviously, the XmlReader has no competition. The competition is between the DataReader and the DataSet. You use a DataReader when you need to simply read data in data types or streams—for example, when you need to display data on a system console. You use a DataSet when you need to display data in data-bound controls and do something more with the data such as editing and deleting data through a DataGrid control.

Note

For more information, read "Building Distributed Applications with Microsoft .NET" at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/bdadotnetarch13.asp.

Updating a Data Source: Command vs. DataSet

You have two approaches for adding and updating data to a data source: using commands or using a DataSet. For example, if you want to use a command, you construct a SQL statement (INSERT, UPDATE, or DELETE) and execute the command using SqlCommand or another data provider's Command object. If you want to use a DataSet, you go through the DataSet and call the AddNew, Edit, or Delete members of a DataTable to add, update, and delete (respectively). Both of the approaches have their uses. Executing commands give you control over the entire table, and using the DataSet approach limits you to a record. For example, you can construct UPDATE and DELETE SQL statements and execute them using commands to update and delete all records of a table. On the other hand, a DataTable provides a way to access all records in the form of a collection, where you can loop through these records one by one and do whatever you want to do with them.

So, again, which one should you choose? It depends on your application requirements. If you're using a DataGrid bound to a DataSet and want to delete a particular record, your obvious choice would be to use the DataTable.Rows.Delete method. But if there's no DataSet available in your application and you want to delete a record based on some criteria, you can simply construct a DELETE statement with a WHERE clause and execute it using the Command object.

If you want to add a record to a database, executing a command is faster than a DataSet. This is also the case with delete and update operations.

Saving Data and Using GetChanges

When using a DataAdapter.Update method, it's a good idea to send only updated records back to the database. The GetChanges method of the DataSet returns the updated records. See FAQ 18 of Appendix C for more details.

Retrieving Data with the SELECT Statement

Using SELECT * FROM TableName is a common statement to retrieve data from a database. It doesn't matter whether you read data through a DataReader or a DataSet. You don't even have to think about what columns a database table has.

In most of the cases, you don't need all the columns of a table. Don't use SELECT * unless you really need all data. Instead, use your column names. For example, if you want to select only the FirstName and LastName columns of the Employees table, use SELECT FirstName, LastName FROM Employees instead of SELECT * FROM Employees.

Using Stored Procedures

Stored procedures are highly optimized objects and reduce network traffic, code size, execution time, and server overhead. Use store procedures instead of creating SQL commands if possible.

Using Connection Strings and Pooling

ADO.NET sends all Connection objects to a pool after a connection closes. When there's a need for a connection, instead of creating a new connection, you use a connection from the pool. But there's a limitation in connection pooling: The connection string must be the same. This means, if possible, you should store a connection string as a program-level scope variable and use it instead of creating a dynamic connection string each time you create a connection.