运用 ADO.NET 对象优化数据查询代码

    毫无疑问，ADO.NET 向人们提供了一种功能强大、仿真数据库的对象模型，它可以将数据记录保存到内存中。尤其是ADO.net 的 DataSet 类，它不但在功能上相当于数据库表的集中存储器(central repository)，而且支持表间的各种约束和逻辑关系。进一步说来，DataSet 对象其实是一种离线数据容器。

    乍一看，只要把 DataSet 类的所有特性联合起来，就能消除 SQL 查询命令中的复杂子句，比如那些泛滥成灾且层层嵌套的 INNER JOIN子句或者 GROUP BY 子句等。复杂的子句可以分解成两个或更多个相互独立的简单子句，而将每个简单子句的查询结果分别保存在不同的 DataTable 对象中；以后只要分析这些内存数据之间的约束和逻辑关系，就能重建原先表之间必要的“参照完整性”(referential integrity)。

    举个例子：你可以把客户(Customers)表与订单(Orders)表分别保存到两个不同的 DataTable 对象中，然后通过 DataRelation 对象进行绑定 (bind) 。这样, SQL Server (或其它 DBMS 系统) 就免除了 INNER JOIN 子句带来的沉重负担；更重要的是，网络传输负荷也因此而大大减轻。象这样简化 SQL 查询的方案固然行之有效，却并不一定总是最佳选择，尤其是当你的数据库规模庞大而且更新频繁时。

    本文将为大家介绍另一种用于简化 SQL 查询的技术，它充分利用 ADO.NET 的内存数据对象减轻了用户和 DBMS 系统的负担。

    分解 SQL 查询命令

    许多有关 ADO.NET 的书籍，比如 David Sceppa 的大作《Programming ADO.NET Core Reference》(微软出版社)，都建议把复杂的 SQL 查询命令分解成若干简单的子查询，然后把各个子查询的返回结果分别保存到同一个 DataSet 容器内部的若干个 DataTable 对象中。请看一个实例。

    假设你需要获取一些客户订单信息，要求订单是提交于指定年份而且按客户进行分组，还要求订单中至少包含 30 件商品。同时，你还希望获取每个订单的提交者(employee)名字以及客户(customer)的公司名。你可以用下列 SQL 查询语句来实现它：

DECLARE @TheYear int
SET @TheYear = 1997

SELECT o.customerid, od.orderid, o.orderdate, o.shippeddate,
SUM(od.quantity*od.unitprice) AS price,
c.companyname, e.lastname FROM Orders AS o
INNER JOIN Customers AS c ON c.customerid=o.customerid
INNER JOIN Employees AS e ON e.employeeid=o.employeeid
INNER JOIN [Order Details] AS od ON o.orderid=od.orderid
WHERE Year(o.orderdate) = @TheYear AND od.orderid=o.orderid
GROUP BY o.customerid, c.companyname, od.orderid,
o.orderdate, o.shippeddate, e.lastname
HAVING SUM(od.quantity) >30
ORDER BY o.customerid

    暂且抛开你所用的 ADO 或者 ADO.NET吧。用最原始的命令提交方式执行上述 SQL 查询，可以看到如图 1 所示的结果集：

    图 1. 第一个 SQL 查询命令的输出结果，由 SQL Server Query Analyzer 生成并显示。

    在本次查询中，以一条子句为核心，而另外两条 INNER JOIN 子句起辅助作用。核心子句的功能是从数据库中查询所有提交于指定年份、至少包含 30 件商品的订单。核心子句如下：

SELECT o.customerid, o.orderid, o.orderdate,
o.shippeddate, SUM(od.quantity*od.unitprice) AS price, o.employeeid
FROM orders AS o
INNER JOIN [Order Details] AS od ON o.orderid=od.orderid
WHERE Year(o.orderdate) = @TheYear AND od.orderid=o.orderid
GROUP BY o.customerid, o.orderid, o.orderdate, o.shippeddate,
o.employeeid
HAVING SUM(od.quantity) >30
ORDER BY o.customerid

    在返回结果集中，客户和提交者均用 ID 来表示。然而，本例需要的是客户的公司名(compayname)和提交者的名字(lastname)。末尾的 ORDER BY o.customerid 语句显得特别简单，可是其功能却很重要：由于客户公司名和提交者名字所含的字符较多，使用该语句就能避免它们的重复出现，从而得到更紧凑的结果集。

    综上所述，整个 SQL 查询可以被分解成 3 条子查询命令―― 1 条核心子查询，用于获取订单记录；2 条辅助子查询，用于建立提交者ID - 提交者名字和客户ID - 客户公司名两个对照表，即：

SELECT employeeid, lastname FROM Employees
SELECT customerid, companyname FROM Customers

    以下 ADO.NET 代码演示了如何把这 3 条子查询的返回结果集保存到 DataSet 对象中。

Dim conn As SqlConnection = New SqlConnection(connString)
Dim adapter As SqlDataAdapter = New SqlDataAdapter()

conn.Open()
adapter.SelectCommand = New SqlCommand(cmdCore, conn)
adapter.SelectCommand.Parameters.Add("@TheYear", 1997)
adapter.SelectCommand.Parameters.Add("@TheQuantity", 30)
adapter.Fill(ds, "Orders")
adapter.SelectCommand = New SqlCommand(cmdCust, conn)
adapter.Fill(ds, "Customers")
adapter.SelectCommand = New SqlCommand(cmdEmpl, conn)
adapter.Fill(ds, "Employees")
conn.Close()

    请注意：在连续执行 SQL 查询命令时，你通常都要自行操作数据库连接，以免出现多余的open/close 操作。本例的 adapter.Fill 方法会自动执行 open/close 操作，除非你设置 adapter.SelectCommmand 属性把某个连接显式关联到 adapter 对象之上。

    为了建立内存数据表之间的关系链，你可以创建两个关系，把 employeeid (提交者的ID) 关联到 lastname (提交者的名字)，把 customerid (客户的ID) 关联到 companyname (客户的公司名)。一般情况下，可以用 DataRelation 对象在同一 DataSet 对象内创建两个独立表之间的一对多关系。然而，本例却需要建立多对一关系，这是很少见的。其实，只要把一对多关系中的父表(Orders) 变成子表，而把子表(Employees、Customers) 变成父表就行了。

     图 2. 关系中的父表与子表角色互换

ADO.NET 中的 DataRelation 对象相当灵活，足以构建多对一关系。每生成一个DataRelation 对象，ADO.NET 都会在后台为之建立一致性约束，以免父表内的键值重复。当然了，一旦重复的键值出现，ADO.NET 就会抛出(throw)一个例外。请看下列代码：
Dim relOrder2Employees As DataRelation
relOrder2Employees = New DataRelation("Orders2Employees", _
ds.Tables("Orders").Columns("employeeid"), _
ds.Tables("Employees").Columns("employeeid"))
ds.Relations.Add(relOrder2Employees)
此处的 DataRelation 对象构造器初始化了三个参数：第一个是关系名称，后面两个是 DataColumn 对象，分别代表构成关系的两个列(column)：前一个 DataColumn 对象代表父列，后一个 DataColumn 对象代表子列。一旦构造器发现父列中不存在合法记录，便会激活(raise) 一个 ArgumentException 例外。消除此例外最简单的解决方案是在构造器中添加一个布尔值作为第四参数：
relOrder2Employees = New DataRelation("Orders2Employees", _
ds.Tables("Orders").Columns("employeeid"), _
ds.Tables("Employees").Columns("employeeid"), _
False)

    当构造器的第四参数值为 false 时，ADO.NET 就不会建立一致性约束，而后者正是引发 ArgumentException 例外的罪魁祸首。

    设置了数据关系之后，你就可以用列表达式 (computed column) 给 Orders 表添加两列以显示其内容了。理论上，这么做完全符合逻辑：

Dim orders As DataTable = ds.Tables("Orders")
orders.Columns.Add("Employee", GetType(String), _
"Child(Orders2Employees).lastname")
orders.Columns.Add("Customer", GetType(String), _
"Child(Orders2Customers).companyname")

    可惜，它根本行不通。更糟的是，当它运行到包含 Child 的代码时，就会抛出 (throw) 一条 “句法错误”信息，而这条出错信息很容易误导程序员。(有关列表达式的更多信息，请参阅《last month's column》。)

    为什么会出错？因为只有当父列存在一致性约束时，才允许在列表达式中使用 Child 关键字。这一点在开发文档中并未明确指出，然而它却是事实，而且非常重要。令人不解的是，你不但可以顺利地访问 Orders 表任一行的子元素，还能直接访问 Employees 表或 Customers 表的任一列。以下代码可以证明这一点：

Dim orders As DataTable = ds.Tables("Orders")
Dim employee As DataRow = orders.Rows(0).GetChildRows(relOrder2Employees)
MsgBox employee("lastname")

    因此，所谓的“句法错误”，并不代表你无法建立多对一关系。它只是提醒你：除非事先建立一致性约束，否则就不能在列表达式中使用 Child 关键字。

    在初始关系中，Orders 表是父表。然而，为了从 ID 获取提交者名字或客户公司名，你就必须改变诸表所扮演的角色：让 Orders 表充当子表，而让 Employees 表、Customers 表充当父表。为了确保做到这一点，你必须改变 DataRelation 对象构造器代码中的列名，并且象这样使用列表达式：

Dim orders As DataTable = ds.Tables("Orders")
orders.Columns.Add("Employee", GetType(String), _
"Parent(Orders2Employees).lastname")
orders.Columns.Add("Customer", GetType(String), _
"Parent(Orders2Customers).companyname")

    小结：在本例中，我们把一个复杂的 SQL 查询分解成 3 个较为简单的子查询，从而消除了两个 INNER JOIN 语句，减轻了数据库服务器的负担；更重要的是，大大减少了从服务器到客户端的网络传输负荷。看来，这似乎是最好的解决方案了？

    替代方案

    前面的解决方案是以对照表为基础的，而且在对照表的生成过程中没有进行数据过滤。一旦对照表的规模过大，会有什么后果呢？难道你愿意为了得到区区数百个提交者的名字就从服务器下载 10,000 条记录？难道你甘心下载那一大堆冗余数据？更何况，那些冗余数据对你毫无用处！

    可是，请换个角度想一想。对照表在应用程序的整个生命周期中往往都是有价值的。也换言之，虽然对单独一次查询来说，下载许多记录以构建完整的对照表未免过于奢侈，但是它对整个应用程序来说也未必不是公平交易。

    既然这样，我们何不尝试用另一种技术来缩减对照表的规模呢？最容易想到的方案莫过于借助 WHERE 子句来缩小结果集了。非常不幸，此方案要么难以实现，要么效果欠佳，尤其是在对照表诸列并不包括你所要查询的对象时。例如：为了对提交者的名字进行过滤，你就必须对其它表进行联合查询――比如 Order 表和 Order Details(订单细节) 表。我认为，最佳方案是重新获取上次 SQL 查询的返回结果集，并从中解析出每个提交者的信息。也就是说，完成前述 SQL 查询之后，再次发送一个几乎相同的查询命令，令数据库服务器重新运行分解后的子查询。这样，数据库将以最小的查询代价返回完全相同的数据。更妙的是，SQL 服务器还特别设置了查询优化引擎，使得此类重复查询的代价减到最低。

SELECT DISTINCT t.customerid, t.companyname FROM
(SELECT o.customerid, o.orderid, o.orderdate, o.shippeddate,
SUM(od.quantity*od.unitprice) AS price,
c.companyname, o.employeeid
FROM orders AS o
INNER JOIN Customers AS c ON c.customerid=o.customerid
INNER JOIN [Order Details] AS od ON o.orderid=od.orderid
WHERE Year(o.orderdate) = @TheYear AND od.orderid=o.orderid
GROUP BY o.customerid, o.orderid, c.companyname,
o.orderdate, o.shippeddate, o.employeeid
HAVING SUM(od.quantity) >30) AS t

    总而言之，以若干简单查询为基础而设计的资料检索代码最大的优点是：它把数据联结 (joining) 的重任由服务器转移到了客户端。另一方面，由于客户端把数据记录分布于几个相互独立、易于链接的表内，因而数据查询操作异常灵活。

    用一些短小、简单的 SQL 查询从数据库读取记录并分别保存到额外的DataTable 对象中，这对客户端应用程序来说是一个好消息。可是，如果这些子查询返回的部分数据不符合一致性约束，那又会如何呢？

    事务的应用

    通常，每个查询命令，无论它有多复杂，都是在同一个默认的事务(transaction)中执行的。正因为如此，它才能确保在执行过程中不会因为其它代码的干扰而破坏数据的整体一致性。可是，假如你把作为逻辑整体的查询命令分解成为若干子查询，结果又会怎么样？

    不妨假设你的软件需要处理一个富于变化的环境，而且数据库中的记录也在迅速更新。或许你的子查询还在执行过程中，数据就已经被某个用户替换了。在前面的范例程序中，这种意外尚且不会造成多大的损失，因为第二个和第三个子查询命令仅仅处理已经生成的对照表。尽管如此，一旦有人在你获取订单数据后把某个提交者的记录删除了，你所查询到的数据还是可能违背一致性约束。因此，必须把分解得到的子查询及相关的处理代码全部放入同一个事务中运行。除此之外，你别无选择。

    所谓事务(Transaction)，是指一组操作，它在执行时严格遵守下列规则：

    ?不可分性(原子性 Atomicity)
    ?一致性(Consistency)
    ?独立性(Isolation)
    ?持续性(Durability)

    人们通常提取这 4 条规则( 4 种属性) 的首字母合称 ACID 。对本例而言，最重要的规则(属性)是独立性。所谓独立性，是指数据库有能力确保每个正在运行中的事务不被任何其它并行事务所干扰。当你的查询命令正在某个事务中运行的时候，如果其它用户的数据库操作也在别的事务中同时运行，则你最终得到的结果将与事务的独立性等级有关。正常情况下，数据库能根据每个事务中的操作合理分配独立性等级。如果应用程序要求数据保持绝对的一致性，绝不容许出现“虚幻行”(phantom rows) 时，它就必须获得“可串行”(serializable) 独立等级。

    当一个“可串行”的事务在运行时，它将锁定一切相关表，防止任何其他用户更新或插入字段。只有当事务运行完毕时，表才会解锁。在此独立性等级下，“读污染”(dirty reads，即读入未授权的数据) 和“虚幻行”(phantom rows，即尚无记录的行，或者已被其它事务删除的行) 自然不会出现；可是，数据的整体一致性仍然无法确保。

    既然你的子查询命令在运行过程中可能出现提交者记录被删除、订单记录被修改等情况，那么你当然应该把所有子查询都包裹到一个“可串行”的事务中。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
-- get Orders
-- get Customers
-- get Employees
COMMIT TRANSACTION

    再强调一次，如果条件允许，你的事务就应该具有“可串行”独立性等级。如果你担心锁定全部表会带来不利影响的话，请不妨试试 ADO.NET 的内存数据对象。稍后我们将讨论这些对象。

    解除数据联结

    让我们回过头来看一看本文开头那个查询范例。它的目标是从数据库中读取在指定年份提交且所含商品件数符合条件的所有订单记录；我们也需要知道订单的总数、客户的公司名和订单提交者的名字。
DECLARE @TheYear int
DECLARE @TheAmount int
SET @TheYear = 1997
SET @TheAmount = 30

SELECT o.customerid, od.orderid, o.orderdate, o.shippeddate,
SUM(od.quantity*od.unitprice) AS price,
c.companyname, e.lastname FROM Orders AS o
INNER JOIN Customers AS c ON c.customerid=o.customerid
INNER JOIN Employees AS e ON e.employeeid=o.employeeid
INNER JOIN [Order Details] AS od ON o.orderid=od.orderid
WHERE Year(o.orderdate) = @TheYear AND od.orderid=o.orderid
GROUP BY o.customerid, c.companyname, od.orderid,
o.orderdate, o.shippeddate, e.lastname
HAVING SUM(od.quantity) >@TheAmount
ORDER BY o.customerid

    以上 SQL 查询命令确实可以一次性地返回全部所需资料。只要让它们在同一个事务中运行，就能确保返回数据的一致性和“可串行”性。然而此方案已经过时，我们也没有选用它。为什么呢？

    事实上，它存在两个问题：第一，返回结果集各行分别来自 3 个不同的表：

    ?订单 (Orders)
    ?客户 (Customers)
    ?提交者 (Employees)

    这还不包括 Order Details 表。

    第二，INNER JOIN 语句造成一些不必要的数据移动。我们无法解决第二个问题，可是某些 ADO.NET 代码却有助于解决第一个问题。因此，我们仍然有机会提高整个解决方案的可行性和有效性。

    具体思路如下：首先执行 SQL 查询，将返回结果集保存到一个 DataTable 对象中；然后把 DataTable 中的数据分散到 3 个不同却又相关的 DataTable 对象中。最终输出的结果与分别查询诸表没什么区别，可它却节省了定义和设置“可串行”事务的开销，同时也避免了从数据库下载多余记录；美中不足是每一行都可能包含少量冗余信息。

    何时可以采用本方案呢？我发现，当客户端需要借助 group-by 函数及各种过滤器来组建一个复杂的“主从复合结构”(master/detail) 视图时，本方案是一个不错的选择。顺便提一句，此时采用多个不同而相关的表是非常有效的，ADO.NET 也为此提供了不少优化特性。

    我们来讨论具体操作。以下代码示范了它的主要流程：

Function SplitData(ByVal ds As DataSet) As DataSet
Dim _dataset As New DataSet()

' Make a full worker copy of the DataSet
_dataset = ds.Copy()

CreateCustomers(_dataset, ds)
CreateEmployees(_dataset, ds)

' Remove columns from Orders(companyname [2] and lastname [4])
_dataset.Tables("Orders").Columns.RemoveAt(1)
_dataset.Tables("Orders").Columns.RemoveAt(3)

Return _dataset
End Function

    代码首先完整地复制了 DataSet 对象(ds)，以它作为新 DataSet 对象(_dataset) 中的Orders 表。接下来，代码又在新 DataSet 对象中动态添加了 customers 表和 employees 表。最后，它又从新 DataSet 对象的 Orders 表中删除了其它两个子表所包含的列。下图显示了新 DataSet 对象中 customers 表的内容。瞧，它只留下了 Orders 表中 (所有) 订单的客户 ID 和公司名两列。由于这两个表都有 customerid 列，故它们仍然可以建立关系。

    图 3 . 根据第一次查询的返回结果新生成的 Customers 表

    下面来简单谈谈用于创建和填充 customers 表与 employees 表所必需的代码。

    一开始，你必须调用 clone 方法克隆原先的订单表以创建一个新的 DataTable 对象。与Copy 方法不同，Clone 方法仅仅复制元数据(metadata)。由于 DataTable 接口不允许克隆单个列，所以本方法是生成对等表的最简单途径。然而，这样生成的表将包含某些多余列，我们必须删除之。

    只要分析第一个 DataSet 对象的结构，你就会发现 customerid 列和 companyname 列正是返回结果集的第一列和第二列。

Dim _customers As DataTable = orig.Tables("Orders").Clone()
_customers.TableName = "Customers"

' Remove unneeded columns
Dim i As Integer
For i = 2 To _customers.Columns.Count - 1
_customers.Columns.RemoveAt(2)
Next

    建立表结构之后，还得载入数据。然而，在 Orders 表中可能多次出现同一个提交者。此外，你必须对源 DataSet 对象中的数据加以过滤。好在 Orders 表已经根据 customerid 列进行排序，所以你只要循环遍历所有行，从中选出符合条件者即可。

Dim row As DataRow
Dim customerKey As String = ""
For Each row In _dataset.Tables("Orders").Rows
' Already sorted by CustomerID
If customerKey <> row("customerid") Then
' select distinct
_customers.ImportRow(row)
customerKey = row("customerid")
End If
Next

' Add to the DataSet
_dataset.Tables.Add(_customers)

    ImportRow 是从数据库导出指定行到新表的最快途径。通常，ImportRow 方法会根据模式 (schema) 的要求来挑选被导出的列。

    原则上，employeess 表的创建和 customers 表的创建大体相同。当然了，你应该删除的列有所不同。从 Orders 表的结构来分析，我们必须保留第 3 列与第 4 列。下列代码首先删除第1列和第2列，然后用一个循环解决其它列。

Dim _employees As DataTable = orig.Tables("Orders").Clone()
_employees.TableName = "Employees"

' Remove unneeded columns
_employees.Columns.RemoveAt(0)
_employees.Columns.RemoveAt(0)
Dim i As Integer
For i = 2 To _employees.Columns.Count - 1
_employees.Columns.RemoveAt(2)
Next

    最后，你还必须清除 employees 表中的重复行。在本例中，对 Orders 表的排序有助于简化该操作。你可以先创建 Orders 表的已排序视图(sorted view)，然后循环遍历所有行。

Dim employeeKey As Integer = 0
Dim view As DataView = New DataView(_dataset.Tables("Orders"))
view.Sort = "employeeid"

Dim rowView As DataRowView
For Each rowView In view
If employeeKey <> Convert.ToInt32(rowView("employeeid")) Then
' select distinct
_employees.ImportRow(rowView.Row)
employeeKey = Convert.ToInt32(rowView("employeeid"))
End If
Next

' Add to the DataSet
_dataset.Tables.Add(_employees)

    总结

    本文示范了一个复杂的 SQL 查询实例，并讨论了 3 种提高其效率的方案。不得不承认，经典的 ADO 对于此类问题的解决帮助有限，而 ADO.NET 却能让你构造一种功能强大的离线数据对象模型以提高程序性能。本文提到了几种解决方案，究竟哪种是最佳选择？很难说。影响运行效率的因素非常多，比如：网络的有效带宽，数据的继承一致性，程序对数据稳定性的要求，等等。为了确定最佳方案，你就必须逐一尝试各种方案，分别测试其性能。

查看本文来源