基于集合和过程的方法示例

引言

在本文中，我将使用一些简单的示例来比较基于集合与过程化方法。

要运行这些脚本，您需要 AdventureWorks 数据库，可以从这里下载。 以下是这些示例中将使用的表的图示：

Contact – 19972 行
SalesOrderHeader – 31465 行

任务

仅出于测试目的，假设我们需要获取所有联系人的列表，并为每个联系人获取 SalesOrderHeader 表中 SubTotal 列的总和。 结果表应包含以下列

ContactID、LastName、FirstName、TotalSales

测试

获取 ContactID、FirstName 和 LastName 非常简单，我们只需查询 Contact 表即可获得结果。 现在如何获取每个联系人的 TotalSales 信息？ 如果我们遵循“过程化”方法，那么一种解决方案可能是编写一个标量 UDF 并在查询中使用它。 以下是 UDF 脚本

CREATE FUNCTION dbo.GetContactTotalSales
(
	@contactID int
)
RETURNS money
AS
BEGIN
	DECLARE @contactTotalSales money;		

	SET @contactTotalSales = (SELECT Sum(SubTotal) _
		FROM Sales.SalesOrderHeader WHERE ContactID = @contactID);

	RETURN 	@contactTotalSales;
END 

现在，最终查询将如下所示

SELECT  
	c.ContactID,
	c.LastName,
	c.FirstName,
	dbo.GetContactTotalSales(c.ContactID) as TotalSales
FROM
	Person.Contact c 

为了测试每个查询，我使用了 SQL Server Profiler。 我采用了标准模板，只是在其中添加了查询执行计划。 此外，在运行每个查询之前，我执行了：

DBCC DROPCLEANBUFFERS 

这会清除缓冲池中的缓冲区。 否则，第二次调用将比前一次调用快得多，因为 SQL Server 将从缓存中返回结果。 运行查询后，我得到了以下结果

以下是这些列的含义

• CPU - 事件使用的 CPU 时间量（以毫秒为单位）
• Reads - 服务器代表事件执行的逻辑磁盘读取次数
• Duration - 事件所用的时间量（以微秒为单位）

为了了解这些结果有多糟糕，我们需要将其与“基于集合”的变体的结果进行比较。 以下是查询脚本

SELECT  
	c.ContactID,
	c.LastName,
	c.FirstName,
	ts.TotalSales as TotalSales
FROM
	Person.Contact c
LEFT OUTER JOIN
	(
		SELECT     
			soh.ContactID, SUM(soh.SubTotal) AS TotalSales
		FROM         
			Sales.SalesOrderHeader soh
		GROUP BY 
			soh.ContactID
	) as ts ON ts.ContactID = c.ContactID

运行查询后，我得到了以下结果

• CPU: 203
• READS: 1273
• DURATION: 787

如果我们将“基于集合”和“过程化”查询的持续时间进行比较，那么我们得到过程化查询慢了大约 159 倍。 这是一个巨大的差异！

使用“过程化”方法并不总是那么糟糕。 对于少量行，查询的性能可能与“基于集合”的查询相似甚至更好。 如果无法以“基于集合”的形式编写查询，则以“过程化”形式编写，但请确保不要返回太多行。 此数字应通过测试为每个特定情况单独确定。

还有另一种编写查询的方法，我想讨论一下。 内联查询可以放置在 select 部分中。

 SELECT  
	c.ContactID,
	c.LastName,
	c.FirstName,
	(
		SELECT     
			SUM(soh.SubTotal)
		FROM         
			Sales.SalesOrderHeader soh
		WHERE
			soh.ContactID = c.ContactID
	) as TotalSales
FROM
	Person.Contact c 

许多开发人员会认为这是使用“过程化”方法的另一种编写查询的方式。 但是，结果显示相反

• CPU: 140
• READS: 1273
• DURATION: 794

我们可以看到，结果与“基于集合”的查询几乎相同。 如果我们比较查询计划，那么我们可以看到查询计划几乎相等，只有微不足道的差异。

“基于集合”查询

“select 中的内联查询”查询

如您所见，唯一的区别是“计算标量”操作的位置。 我可以得出的结论是，SQL Server 足够聪明，可以将此类查询作为“基于集合”的查询处理。 无论如何，我不建议向 select 添加内联查询，因为我注意到，当查询变得更复杂时，SQL Server 开始处理它们比等效的“经典基于集合”查询更糟糕。

使用视图改进“基于集合”的查询

使用“过程化”方法的优势之一是它使查询更具可读性。 很容易将复杂的查询移动到 UDF 并在其他查询中重用它。 另一方面，“基于集合”的查询往往更复杂，难以编写和阅读。 改进“基于集合”查询的简单方法是使用视图。 视图允许您执行与 UDF 相同的功能（封装 SQL 脚本），但没有任何开销。 因此，如果我们创建以下视图

CREATE VIEW dbo.ContactTotalSales
AS
	SELECT     
		ContactID, SUM(SubTotal) AS TotalSales
	FROM         
		Sales.SalesOrderHeader
	GROUP BY 
		ContactID 

那么我们可以使我们的查询看起来像这样

SELECT  
	c.ContactID,
	c.LastName,
	c.FirstName,
	ts.TotalSales
FROM
	Person.Contact c
LEFT OUTER JOIN
	ContactTotalSales ts ON ts.ContactID = c.ContactID 

如果我们比较此查询的性能，我们将看到它等于原始“基于集合”的查询。

结论

始终测试查询的性能并检查其执行计划。 使用视图改进复杂的查询结构。 非常小心地使用“过程化”方法，只有在没有其他选择的情况下才使用它。

感谢您的阅读。 我希望这对您有用。