ADO.NET and SQL Server Performance Tips

ADO.NET provides several different methods to access SQL Server data, including OLE DB.NET, ODBC.NET, SQLXML, and the SQL Server .NET data provider. Of all of these, the SQL Server .NET data provider is the fastest, as much as 30-40% faster than the others. The SQL Server .NET provider uses TDS (Tabular Data Stream, which is the native SQL Server data format) to communicate with SQL Server. The SQL Server .NET provider can be used to connect to SQL Server 7.0, SQL Server 2000, and SQL Server 2005 databases, but not SQL Server 6.5 databases. If you need to connect to a SQL Server 6.5 database, the best overall choice is the OLE DB.NET data provider. [7.0, 2000, 2005] Updated 2-11-2005 

*****

When using ADO.NET to make connections to SQL Server, always be sure you explicitly close any Connection, Recordset, or Command objects you have opened. While letting an object go out of scope will in affect close the object, it is not the same as explicitly closing an object. By explicitly closing these objects and setting them to nothing, you do two things. First, you remove the object sooner than later, helping to free up resources. Second, you eliminate the possibility of "connection creep". Connection creep occurs when connection or resource pooling is used and when connections are not properly closed and released from the pool. This helps to defeat the purpose of pooling and reduces SQL Server's performance. [7.0, 2000, 2005] Updated 2-11-2005

*****

When you specify a server in an ADO.NET connection string, use the server's IP address, not the server's DNS name. By using an IP address instead of a DNS name, name resolution does not have to occur, reducing the amount of time it takes for a connection to be made. A server's IP address can be used to specify either a default or named instance of a server running SQL Server. If you are running a cluster, use the virtual SQL Server IP address. [7.0, 2000, 2005] Updated 2-11-2005

*****

While SQL Server application roles are handy, they can also negatively affect your application's performance. The reason for this is that a connection to SQL Server using an application role cannot take advantage of connection pooling. In effect, connection pooling is turned off for any connections using application roles. If your application will be making many connections to SQL Server, avoid applications roles for your application's connections. [7.0, 2000] Updated 2-11-2005

*****

To get the most out of connection pooling in ADO.NET, keep the following in mind when developing your applications:

• Be sure than your connections use the same connection string each time. Connection pooling only works if the connection string is the same. If the connection string is different, then a new connection will be opened.
• Only open a connection when you need it, not before.
• Close your connection as soon as you are done using it.
• Don't leave a connection open if it is not being used.
• Be sure to drop any temporary objects before closing a connection.
• Be sure to close any user-defined transactions before closing a connection.
• Don't use application roles if you want to take advantage of connection pooling.

[7.0, 2000, 2005] Added 2-25-2002

*****

Disconnected recordsets in ADO.NET outperform disconnected recordsets in traditional ADO. ADO.NET is faster than ADO for disconnected recordsets because under ADO, COM marshalling between tiers requires that values in a recordset be converted to values recognized by COM. Under ADO.NET, data type conversion is not required, boosting performance. [7.0, 2000] Added 2-25-2002

*****

When possible, use the ExecuteNonQuery method with SQLCommand objects, as this is the most efficient way to execute queries from ADO.NET. Use output parameters with SQLCommand objects if you need to retrieve just a few values, or a single data row, instead of using more expensive techniques, such as a SQLDataAdapter, a SQLDataReader, or a strongly typed DataSet. [2000, 2005] Added 3-27-2002 Read an article about ADO.NET.