SQLServer性能优化 .net开发菜鸟总结

一、 ExecuteNonQuery和ExecuteScalar 对数据的更新不需要返回结果集，建议使用ExecuteNonQuery。由于不返回结果集可省掉网络数据传输。它仅仅返回受影响的行数。如果只需更新数据用ExecuteNonQuery 性能 的开销比较

一、 ExecuteNonQuery和ExecuteScalar
对数据的更新不需要返回结果集，建议使用ExecuteNonQuery。由于不返回结果集可省掉网络数据传输。它仅仅返回受影响的行数。如果只需更新数据用ExecuteNonQuery性能的开销比较小。
ExecuteScalar它只返回结果集中第一行的第一列。使用 ExecuteScalar 方法从数据库中检索单个值（例如id号）。与使用 ExecuteReader 方法， 返回的数据执行生成单个值所需的操作相比，此操作需要的代码较少。
只需更新数据用ExecuteNonQuery.单个值的查询使用ExecuteScalar 。

二、 SqlDataRead和Dataset的选择
Sqldataread优点：读取数据非常快。如果对返回的数据不需做大量处理的情况下，建议使用SqlDataReader，其性能要比datset好很多。缺点：直到数据读完才可close掉于数据库的连接 。(SqlDataReader 读数据是快速向前的。SqlDataReader 类提供了一种读取从 SQL Server 数据库检索的只进数据流的方法。它使用 SQL Server 的本机网络数据传输格式从数据库连接直接读取数据。DataReader需及时显式的close。可及时的释放对数据的连接。)
Dataset是把数据读出，缓存在内存中。缺点：对内存的占用较高。如果对返回的数据需做大量的处理用Dataset比较好些可以减少对数据库的连接操作。优点：只需连接一次就可close于数据库的连接。
一般情况下,读取大量数据,对返回数据不做大量处理用SqlDataReader.对返回数据大量处理用datset比较合适.对SqlDataReader和Dataset的选择取决于程序功能的实现。

数据绑定的选择
三、 数据的绑定DataBinder
一般的绑定方法 用DataBinder.eval 绑定不必关心数据来源(Dataread或dataset)。不必关心数据的类型eval会把这个数据对象转换为一个字符串。在底层绑定做了很多工作，使用了反射性能。正因为使用方便了，但却影响了数据性能。
来看下。当于dataset绑定时，DataItem其实式一个DataRowView（如果绑定的是一个数据读取器（dataread）它就是一个IdataRecord。）因此直接转换成DataRowView的话，将会给性能带来很大提升。.

对数据的绑定建议使用。数据量大的时候可提高几百倍的速度。使用时注意2方面：1.需在页面添加.2.注意字段名的大小写（要特别注意）。如果和查询的不一致，在某些情况下会导致比还要慢。如果想进一步提高速度，可采用的方法。不过其可读性不高。

以上的是vb.net的写法。在c#中：

一、 应用Ado.net的一些思考原则
1. 选择适当的事务类型
2. 使用存储过程
3. 根据数据使用的方式来设计数据访问层
4. 必要时申请，尽早释放
5. 缓存数据，避免不必要的操作
6. 使用服务帐户进行连接
7. 减少往返
8. 仅返回需要的数据
9. 关闭可关闭的资源

二、 Connection
数据库连接是一种共享资源，并且打开和关闭的开销较大。Ado.net默认启用了连接池机制，关闭连接不会真的关闭物理连接，而只是把连接放回到连接池中。因为池*享的连接资源始终是有限的，如果在使用连接后不尽快关闭连接，
那么就有可能导致申请连接的线程被阻塞住，影响整个系统的性能表现。
1、在方法中打开和关闭连接
这个原则有几层含义：
1）主要目的是为了做到必要时申请和尽早释放
2）不要在类的构造函数中打开连接、在析构函数中释放连接。因为这将依赖于垃圾回收，而垃圾回收只受内存影响，
回收时机不定
3）不要在方法之间传递连接，这往往导致连接保持打开的时间过长

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

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

using (SqlConnection conn =new SqlConnection(connString)) { conn.Open(); } // Dispose is automatically called on the conn variable here

3、确保连接池启用
Ado.net是为每个不同的连接串建立连接池，因此应该确保连接串不会出现与具体用户相关的信息。另外，要注意连接串是
大小写敏感的。
4、不要缓存连接
例如，把连接缓存到Session或Application中。在启用连接池的情况下，这种做法没有任何意义。

三、Command
1、 使用ExecuteScalar和ExecuteNonQuery
如果想返回像Count(*)、Sum(Price)或Avg(Quantity)那样的单值，可以使用ExecuteScalar方法。ExecuteScalar返回第一行第一列的值，将结果集作为标量值返回。因为单独一步就能完成，所以ExecuteScalar不仅简化了代码，还提高了性能。
使用不返回行的SQL语句时，例如修改数据（INSERT、UPDATE或DELETE）或仅返回输出参数或返回值，请使用ExecuteNonQuery。这避免了用于创建空DataReader的任何不必要处理。
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、使用绑定变量
SQL语句需要先被编译成执行计划，然后再执行。如果使用绑定变量的方式，那么这个执行计划就可以被后续执行的SQL语句所复用。而如果直接把参数合并到了SQL语句中，由于参数值千变万化，执行计划就难以被复用了。例如上面Prepare一节给出的示例，如果把参数值直接写到insert语句中，那么上面的四次调用将需要编译四次执行计划。
为避免这种情况造成性能损失，要求一律使用绑定变量方式。

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

1、 显式关闭DataReader
与连接类似，也需要显式关闭DataReader。另外，如果与DataReader关联的Connection仅为DataReader服务的话，可考虑使用Command对象的ExecuteReader(CommandBehavior.CloseConnection)方式。这可以保证当DataReader关闭时，同时自动关闭Connection。
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]); }

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

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())

五、DataSet
1、 利用索引加快查找行的效率
如果需要反复查找行，建议增加索引。有两种方式：
1）设置DataTable的PrimaryKey
适用于按PrimaryKey查找行的情况。注意此时应调用DataTable.Rows.Find方法，一般惯用的Select方法不能利用索引。
2）使用DataView
适用于按Non-PrimaryKey查找行的情况。可为DataTable创建一个DataView，并通过SortOrder参数指示建立索引。此后使用Find或FindRows查找行。

一、减少往返行程（Reduce Round Trips）
使用下面的方法可以减少Web服务器和Browser之间的往返行程：
1、为Browser启用缓存
如果呈现的内容是静态的或变化周期较长，应启用Browser缓存，避免发出冗余的http请求。

2、缓冲页面输出
如果可能，则尽量缓冲页面输出，处理结束后再一次传送到客户端，这可以避免频繁传递小块内容所造成的多次网络交互。由于这种方式在页面处理结束之前客户端无法看到页面内容，因此如果一个页面的尺寸较大的话，可考虑使用Response.Flush方法。该方法强制输出迄今为止在缓冲区中的内容，你应当采用合理的算法控制调用Response.Flush方法的次数。

3、使用Server.Transfer重定向请求
使用Server.Transfer方法重定向请求优于Response.Redirect方法。原因是Response.Redirect会向Broswer回送一个响应头，在响应头中指出重定向的URL，之后Brower使用新的URL重新发出请求。而Server.Transfer方法直接是一个简单的服务端调用，完全没有这些开销！
需要注意Server.Transfer有局限性：第一，它会跳过安全检查；第二，只适用于在同一Web应用内的页面间跳转。

二、避免阻塞和长时间的作业
如果需要运行阻塞或长时间运行的操作，可以考虑使用异步调用的机制，以便Web服务器能够继续处理其它的请求。
1、使用异步方式调用Web服务和远程对象
只要有可能就要避免在请求的处理过程中对Web服务和远程对象的同步调用，因为它占用的是的ASP.NET 线程池中的工作线程，这将直接影响Web服务器响应其它请求的能力。

2、考虑给不需要返回值的Web方法或远程对象的方法添加OneWay属性
这种模式能让Web Server调用之后就立即返回。可根据实际情况决定是否使用这种方法。

3、使用工作队列
将作业提交到服务器上的工作队列中。客户端通过发送请求来轮询作业的执行结果。

三、 使用缓存
缓存能在很大程度上决定ASP.NET应用的最终性能。Asp.net支持页面输出缓存和页面部分缓存，并提供Cache API，供应用程序缓存自己的数据。是否使用缓存可考虑下面的要点：
1、识别创建与访问代价较大的数据
2、评估需要缓存数据的易变性
3、评估数据的使用频次
4、 将要缓存数据中易变数据和不变数据分离，只缓存不变数据
5、选择合适的缓存机制（除Asp.net Cache外，Application state和Session state也可以作为缓存使用）

四、 系统资源
1、考虑实现资源池以提升性能
2、明确地调用Dispose或Close释放系统资源
3、不要缓存或长时间占用资源池中的资源
4、尽可能晚的申请，尽可能早的释放

五、多线程
1、避免在请求处理过程中创建线程
在执行请求的过程中创建线程是一种代价较大的操作，会严重影响Web Server的性能。如果后续的操作必须用线程完成，建议通过thread pool来创建/管理线程。

2、避免阻塞处理请求的线程

3、避免异步调用
这和1的情况类似。异步调用会导致创建新的线程，增加服务器的负担。所以，如果没有并发的作业要执行，就不要执行异步调用。

4、不要依赖线程数据槽或线程静态变量
由于执行请求的线程是ASP.NET thread pool中的工作线程，同一个Client的两次请求不一定由相同的线程来处理。

六、 页面处理
1、尽量减小Page的尺寸
包括缩短控件的名称、CSS的class的名称、去掉无谓空行和空格、禁用不需要的ViewState
2、优化复杂和代价较大的循环
3、合理利用客户端的计算资源，将一些操作转移到客户端进行
4、启用页面输出的缓冲区（Buffer）
如果Buffer的机制被关闭，可以用下面的方法打开。
使用程序打开页面输出缓存：
Response.BufferOutput = true;
使用@Page开关打开页面输出缓冲机制：

使用Web.config或Machine.config配置文件的节点：

5、利用Page.IsPostBack优化页面输出
6、通过分离页面的不同的内容，来提高缓存效率和减少呈现的时间

七、 ViewState
ViewState是Asp.net为服务端控件在页面回传之间跟踪状态信息而设计的一种机制。
1、在恰当的时间点初始化控件属性
ASP.NET的控件在执行构造函数、初始化的期间设置的属性不会被跟踪变化；而在初始化阶段之后对属性的修改都会被跟踪，并最终记录到IE页面的__VIEWSTATE之中。所以，选择合理的初始化控件属性的执行点，能有效的减小页面尺寸。

2、谨慎选择放到ViewState中的内容
放到ViewState中的内容会被序列化/反序列化，Asp.net为String、Integer、Boolean等基本类型的序列化做了优化，如果Array、ArrayList、HashTable存储的是基本类型效率也较高，但其它类型则需要提供类型转换器（Type Converter），否则将使用代价昂贵的二进制序列化程序。

3、 关闭ViewState
如果不需要跟踪页面状态，例如页面不会 回传（PostBack）、不需要处理服务端控件事件或者每次页面刷新时都会重新计算控件内容，那么就不需要用ViewState来记录页面状态了。可以对特定的WebControl设置EnableViewState属性，也可以在页面一级设置：
// http://www.bjp111.com