ADO.NET and SQL Server Performance Tips

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 and SQL Server 2000 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] Added 2-25-2002

 

ADO.Net은 SQL Server에 접근하기 위한 방법으로  OLE DB.NET, ODBC.NET, SQLXML, SQL Server .NET와 같은 data provider를  제공한다. 이 중 Server .NET data provider가 가장 빠르며, 다른 방법에 비해 약 30~40% 빠르다. Server .NET data provider는 SQL Servr와의 통신에 TDS(Tabular Data Stream, which is the native SQL Server data format)를 사용한다. SQL Server .NET provider는 SQL Server 7.0과 SQL Server 2000 데이터베이스 접속에 사용할 수 있으나 SQL Server 6.5에는 사용할 수 없다. 만약 SQL Server 6.5 접속이 필요하다면 가장 좋은 방법은 OLE DB.NET data provider를 이용하는 것이다.

 

*****

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] Added 2-25-2002

 

*****

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. [ 7.0, 2000] Added 2-25-2002

 

ADO.Net의 Connection String에 서버를 기술할 때, DNS 이름을 사용하지 말고 서버의 IP address를 사용하라. DNS이름 대신 IP address를 사용하게 되면, 이름 풀이(name resolution)를 필요로 하지 않기 때문에, 연결을 만드는 시간을 줄일 수 있다. 서버의 ip address를 기본 인스턴스 혹은 동작중인 SQL Server의 명명된 인스턴스를 기술하는 데 사용할 수 있다.

 

*****

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] Added 2-25-2002

*****

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] 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

 

ADO.NET의 비연결형 recordsets의 성능이 기존의 ADO의 비연결형 recordset보다 성능이 뛰어나다.  ADO.NET의 비연결형 recordset이 ADO의 비연결형 recordset보다 빠르다. 이유는 ADO 하위 계층에서 recordset이 COM이 인식할 수 있는 값으로 변환되는 tier 간의 COM 마샬링(marshalling) 때문이다. ADO.NET 하위 계층에서는 데이터 타입 변환이 필요 없기 때문에 보다 좋은 성능을 발휘한다.

 

*****

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] Added 3-27-2002 Read an article about ADO.NET.

 

가급적 ExecuteNonQuery 메소드와 SQLCommand 객체를 사용하라. 이것이 ADO.Net에서 쿼리를 실행하는 가장 효과적인 방법이다. 몇개의 값을 얻어오거나 하나의 data row를 얻어와야 할 경우에는 SQLDataAdapter, SQLDataReader 혹은 DataSet과 같은 비용이 큰 방법 대신 SQLCommand 객체와 output parameters를 사용하라.

 

출처 : http://www.sql-server-performance.com