ADO.NET性能优化方面的总结

1、 应用Ado.net的一些思考原则

a)      根据数据使用的方式来设计数据访问层；

b)      缓存数据，避免不必要的操作；

c)      使用服务帐户进行连接；

d)      必要时申请资源，尽早释放资源；

e)      关闭可关闭的资源；

f)      减少数据往返；

g)      仅返回需要的数据；

h)      选择适当的事务类型；

i)      使用存储过程；

2、 Connection

数据库连接是一种共享资源，并且打开和关闭的开销较大。Ado.net默认启用了连接池机制，关闭连接不会真的关闭物理连接，而只是把连接放回到连接池中。因为池中共享的连接资源始终是有限的，如果在使用连接后不尽快关闭连接，那么就有可能导致申请连接的线程被阻塞住，影响整个系统的性能表现。

2.1 在方法中打开和关闭连接

这个原则有几层含义：

a)      主要目的是为了做到必要时申请和尽早释放；

b)      不要在类的构造函数中打开连接、在析构函数中释放连接。因为这将依赖于垃圾回收，而垃圾回收只受内存影响，回收时机不定；

c)      不要在方法之间传递连接，这往往导致连接保持打开的时间过长；

这里强调一下在方法之间传递连接的危害：曾经在压力测试中遇到过一个测试案例，当增大用户数的时候，这个案例要比别的案例早很久就用掉连接池中的所有连接。经分析，就是因为A方法把一个打开的连接传递到了B方法，而B方法又调用了一个自行打开和关闭连接的C方法。在A方法的整个运行期间，它至少需要占用两条连接才能够成功工作，并且其中的一条连接占用时间还特别长，所以造成连接池资源紧张，影响了整个系统的可伸缩性！

2.2 显式关闭连接

Connection对象本身在垃圾回收时可以被关闭，而依赖垃圾回收是很不好的策略。推荐使用using语句显式关闭连接，如下例：

using (SqlConnection conn = new SqlConnection(connString))

{

conn.Open();

} // Dispose is automatically called on the conn variable here

 

2.3 确保连接池启用

Ado.net是为每个不同的连接串建立连接池，因此应该确保连接串不会出现与具体用户相关的信息。另外，要注意连接串是大小写敏感的。

2.4 不要缓存连接

例如，把连接缓存到Session或Application中。在启用连接池的情况下，这种做法没有任何意义。

　3、 Command

3.1 使用ExecuteScalar和ExecuteNonQuery

如果想返回像Count(*)、Sum(Price)或Avg(Quantity)那样的单值，可以使用ExecuteScalar方法。ExecuteScalar返回第一行第一列的值，将结果集作为标量值返回。因为单独一步就能完成，所以ExecuteScalar不仅简化了代码，还提高了性能。

　使用不返回行的SQL语句时，例如修改数据（INSERT、UPDATE或DELETE）或仅返回输出参数或返回值，请使用ExecuteNonQuery。这避免了用于创建空DataReader的任何不必要处理。

3.2 使用Prepare

当需要重复执行同一SQL语句多次，可考虑使用Prepare方法提升效率。需要注意的是，如果只是执行一次或两次，则完全没有必要。例如：

cmd.CommandText = "insert into Table1 ( Col1, Col2 ) values ( @val1, @val2 )";

cmd.Parameters.Add( "@val1", SqlDbType.Int,4, "Col1" );

cms.Parameters.Add( "@val2", SqlDbType.NChar,50, "Col2");

cmd.Parameters[0].Value = 1;

cmd.Parameters[1].Value = "XXX";

cmd.Prepare();

cmd.ExecuteNonQuery();

cmd.Parameters[0].Value = 2;

cmd.Parameters[1].Value = "YYY";

cmd.ExecuteNonQuery();

cmd.Parameters[0].Value = 3;

cmd.Parameters[1].Value = "ZZZ";

cmd.ExecuteNonQuery();

3.3 使用绑定变量

SQL语句需要先被编译成执行计划，然后再执行。如果使用绑定变量的方式，那么这个执行计划就可以被后续执行的SQL语句所复用。而如果直接把参数合并到了SQL语句中，由于参数值千变万化，执行计划就难以被复用了。例如上面Prepare一节给出的示例，如果把参数值直接写到insert语句中，那么上面的四次调用将需要编译四次执行计划。

4、 DataReader

DataReader最适合于访问只读的单向数据集。与DataSet不同，数据集并不全部在内存中，而是随不断发出的read请求，一旦发现数据缓冲区中的数据均被读取，则从数据源传输一个数据缓冲区大小的数据块过来。另外，DataReader保持连接，DataSet则与连接断开。

4.1 显式关闭DataReader

与连接类似，也需要显式关闭DataReader。另外，如果与DataReader关联的Connection仅为DataReader服务的话，可考虑使用Command对象的ExecuteReader(CommandBehavior.CloseConnection)方式。这可以保证当DataReader关闭时，同时自动关闭Connection。

4.2 用索引号访问代替名称索引号访问属性

从Row中访问某列属性，使用索引号的方式比使用名称方式有细微提高。如果会被频繁调用，例如在循环中，那么可考虑此类优化。示例如下：

cmd.CommandText = "select Col1, Col2 from Table1" ;

SqlDataReader dr = cmd.ExecuteReader();

int col1 = dr.GetOrdinal("Col1");

int col2 = dr.GetOrdinal("Col2");

while (dr.Read())

{

Console.WriteLine( dr[col1] +"_" + dr[col2]);

}

4.3 使用类型化方法访问属性

从Row中访问某列属性，用GetString、GetInt32这种显式指明类型的方法，其效率较通用的GetValue方法有细微提高，因为不需要做类型转换。

4.4 使用多数据集

部分场景可以考虑一次返回多数据集来降低网络交互次数，提升效率。示例如下：

cmd.CommandText = "StoredProcedureName";// The stored procedure returns multiple result sets.

SqlDataReader dr = cmd.ExecuteReader();

while (dr.read())

// read first result set

dr.NextResult();

while (dr.read())

5、 DataSet

5.1 利用索引加快查找行的效率

如果需要反复查找行，建议增加索引。有两种方式：

设置DataTable的PrimaryKey

适用于按PrimaryKey查找行的情况。注意此时应调用DataTable.Rows.Find方法，一般惯用的Select方法不能利用索引。

使用DataView

适用于按Non-PrimaryKey查找行的情况。可为DataTable创建一个DataView，并通过SortOrder参数指示建立索引。此后使用Find或FindRows查找行。