当连接到SQL Server 7.0及以上版本时,为了获得最佳性能应该使用SQL Server .NET 数据提供程序。SQL Server .NET数据提供程序设计为直接访问SQL Server,没有其它附加的技术层。下图(图1)说明了访问SQL Server 7.0及以上版本的多种技术之间的差别。
使用DataSet的另一个好处是它能被继承用于建立强类型的DataSet。强类型DataSet的好处包括设计时的检查和强类型DataSet 的Visual Studio .NET语句填充。当你为DataSet固定了大纲或关系结构时,就能建立强类型DataSet,把行和列作为对象的属性而不是项的集合。例如,作为暴露顾客表的某一行的列名的代替,你可以暴露Customer对象的 Name属性。强类型的DataSet衍生自DataSet类,因此不会牺牲DataSet的任何功能,也就是说,强类型的DataSet也可以是远程的,并作为数据绑定控件(例如DataGrid)的数据源提供。如果不知道大纲,也能通过使用通常的DataSet获得好处,但是丧失了强类型DataSet的附加特性。
l 避免使用DataAdapter.Fill,它使用了startRecord和maxRecords值。使用这种方式填充数据集时,数据集只填充由maxRecords参数指定的记录个数(从参数startRecord指定的记录开始),而不管返回的整个查询。这导致读取过时的"不想要的"记录,同时使用了不必要的服务器资源来返回补充记录。
l 用于在某个时候只返回一页记录的技术之一是建立一个SQL语句,该语句包含一个WHERE和ORDER BY子句,并有TOP判定。这种技术依赖于识别每个唯一行的方法。当导航到下一页的记录时,修改WHERE子句使它包含所有唯一标识比当前页标识大的记录;当导航到前面一页时,修改WHERE子句使它包含所有唯一标识比当前页标识小的记录。对于两种查询都只返回记录的TOP页的记录。当导航到前面一页时需要对记录进行降序排列,这将返回查询的末尾页(如果需要可以在显示前对记录进行重新排序)。
l 另一种技术是建立一个SQL语句包含TOP判定和嵌入的SELECT语句。这种技术不是基于唯一的识别每行的方法。使用这种技术的第一步是把页面的大小与想得到的页面数量相乘。接着把该数值传递给SQL查询的TOP判定,并按升序排序。接着把这个查询嵌入另一个查询,该查询从嵌入的查询结果中选择TOP页面大小,按降序排列。本质上返回的是嵌入的查询的末尾页面。例如,为了返回页面大小是10的查询结果的第三页,使用下面的命令:
SELECT TOP 10 * FROM
(SELECT TOP 30 * FROM Customers ORDER BY Id ASC) AS Table1
ORDER BY Id DESC
l 如果数据不是经常改变,能通过本地维护数据集里面的记录缓存来提高性能。例如,你能在本地数据集中存储10页数据,只在用户导航超出第一页或最后一页时才查询数据源检索新的数据。
l 命令构造器的使用应该限制在设计时或者ad-hoc情况下。需要的生成数据适配器命令属性的过程妨碍了性能。如果你预先知道INSERT/UPDATE/DELETE语句的内容,应该显式地设置它们。好的设计技巧是为INSERT/UPDATE/DELETE命令建立存储过程并明确地配置数据适配器命令属性来使用它们。
l 命令构造器使用数据适配器的SelectCommand属性来决定其它命令属性的值。如果数据适配器的SelectCommand自身改变了,一定要调用RefreshSchema来更新命令属性。
l 如果命令属性是空的(默认情况下命令属性是空的),命令构造器只为数据适配器命令属性生成一个命令。如果你明确地设置一个命令属性,命令构造器不会覆盖它。如果你希望命令构造器为一个已经设置了的命令属性生成一个命令,要把命令属性设置为空。
'Visual Basic
Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Customers;
SELECT * FROM Orders;", myConnection)
Dim ds As DataSet = New DataSet()
da.Fill(ds, "Customers")
//C#
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Customers;
SELECT * FROM Orders;", myConnection);
DataSet ds = new DataSet();
da.Fill(ds, "Customers");
'Visual Basic
Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Customers;
SELECT * FROM Orders;", myConnection)
da.TableMappings.Add("Customers1", "Orders")
Dim ds As DataSet = New DataSet()
da.Fill(ds, "Customers")
//C#
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Customers;
SELECT * FROM Orders;", myConnection);
da.TableMappings.Add("Customers1", "Orders");
DataSet ds = new DataSet();
da.Fill(ds, "Customers");
使用DataReader
下面是使用DataReader提高性能的一些技巧:
l 在访问任何与命令(Command)相关的输出参数前DataReader必须关闭。
l 在读完数据后就关闭DataReader。如果你正在使用的连接只返回该DataReader,在关闭DataReader后立即关闭连接。
l 另一种明确地关闭连接的方法是给ExecuteReader方法传递CommandBehavior.CloseConnection以确保当DataReader关闭时相关的连接关闭了。如果你从某个方法返回DataReader,并且没有办法控制DataReader或者相关的连接关闭的情况下特别有用。
l DataReader不能在层之间远程访问。DataReader是设计用于连接数据访问的。
l 使用类型化的存取程序(例如GetString、GetInt32等等)来访问列数据。这节省了将GetValue返回的对象作为特定类型的必要的处理。
l 在某一时刻只有一个DataReader能够打开。。在ADO中,如果你打开一个连接并请求两个使用只向前的只读游标的记录集,ADO隐性地为游标的生命周期的数据存储打开第二个不在连接池中的连接,接着隐性地关闭它。在ADO.NET中,如果你想在同一个数据存储上同时打开两个DataReader,你必须明确地建立两个连接,每个DataReader一个。通过这种方法ADO.NET给了你对连接池使用的更多控制。
l 默认情况下,DataReader在每个Read方法中把整个行载入内存中。这允许你随机访问当前行的任意列。如果随机访问是不必要的,为了提高性能,把CommandBehavior.SequentialAccess传递给ExecuteReader调用。这改变了DataReader的默认行为,只在需要时才把数据载入内存。注意CommandBehavior.SequentialAccess要求你按次序访问返回的列。也就是,一旦你读过了返回的某个列,就不能再次读取它的值了。
l 如果你结束了从DataReader中读取数据,但是仍然有大量的未读取的结果等待,那么调用Command的Cancel比调用DataReader 的Close好。调用DataReader 的Close引起它检索等待的结果并且先清空流后关闭游标。调用Command的 Cancel删除服务器上的结果,因此当DataReader关闭时,它不需要再读取结果。如果你从Command返回输出参数,则调用Cancel删除它们。如果你要读取任何输出参数,不要调用Command 的Cancel;最后调用DataReader的 Close。
Command.Prepare方法能够提高数据源中重复的参数化命令的性能。Prepare指示数据源为多个调用优化特定的命令。为了更高效率地使用Prepare,你必须十分清楚数据源怎样回应Prepare调用。对于类似SQL Server 2000的数据源,命令是隐式优化的,对Prepare的调用是没有必要的,但是对于另一些数据源,例如SQL Server 7.0,Prepare效率更高。
明确地指定大纲和元数据
在ADO.NET中当用户没有指定元数据信息时,有很多对象推导这些信息。例如:
l DataAdapter.Fill方法,如果不存在的话,它在记录集中建立表和列。
l CommandBuilder,它为单个的SELECT语句生成数据适配器命令属性。
l CommandBuilder.DeriveParameters,它组合Command对象的Parameters集合。
'Visual Basic
Public Sub RunSqlTransaction(da As SqlDataAdapter,
myConnection As SqlConnection, ds As DataSet)
myConnection.Open()
Dim myTrans As SqlTransaction = myConnection.BeginTransaction()
myCommand.Transaction = myTrans
Try
da.Update(ds)
myTrans.Commit()
Console.WriteLine("Update successful.")
Catch e As Exception
Try
myTrans.Rollback()
Catch ex As SqlException
If Not myTrans.Connection Is Nothing Then
Console.WriteLine("An exception of type "
& ex.GetType().ToString() & _
" was encountered while attempting to roll back the transaction.")
End If
End Try
Console.WriteLine("An exception of type
" & e.GetType().ToString() & " was encountered.")
Console.WriteLine("Update failed.")
End Try
myConnection.Close()
End Sub
//C#
public void RunSqlTransaction(SqlDataAdapter da,
SqlConnection myConnection, DataSet ds)
{
myConnection.Open();
SqlTransaction myTrans = myConnection.BeginTransaction();
myCommand.Transaction = myTrans;
try
{
da.Update(ds);
myCommand.Transaction.Commit();
Console.WriteLine("Update successful.");
}
catch(Exception e)
{
try
{
myTrans.Rollback();
}
catch (SqlException ex)
{
if (myTrans.Connection != null)
{
Console.WriteLine("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}
Console.WriteLine(e.ToString());
Console.WriteLine("Update failed.");
}
myConnection.Close();
}