在这部分里我们所有的例子都选择使用Microsoft SHOWPLAN_ALL输出，因为它更紧凑并且展示典型的信息。（Sybase的查询计划基本与此相同，可能包含其它一些信息）大部分的例子都是要么基于PUBS数据库，要么基于标准系统表的。我们在PUBS数据库中对用到的表进行了很大扩充，对很多表增加了好几万行。
子查询优化
一条好的值得称赞的规则是尽量用连接代替所有的子查询。优化器有时可以自动将子查询“扁平化”，并且用常规或外连接代替。但那样也不总是有效。明确的连接对选择表的顺序和找到最可能的计划给出了更多的选项。当你优化一个特殊查询时，了解一下是否去掉自查询可产生很大的差异。
示例
下面查询选择了pubs数据库中所有表的名字，以及每个表的聚集索引（假如存在）。假如没有聚集索引，表名仍然显示在列表中，在聚集索引列中显示为虚线。两个查询返回同样的结果集，但第一个使用了一个子查询，而第二个使用一个外连接时。比较Microsoft SQL Server产生的查询计划：
不必更深探索，我们可以看到在CPU和总的实耗时间方面连接更快，仅需要子查询方案逻辑读的一半。此外，这两种情况伴随着相同的结果集，虽然排序的顺序不同，这是因为连接查询（由于它的GROUP BY子句）有一个隐含的ORDER BY：
查看这个子查询方法展示的查询计划：
反之，求和查询操作我们可以得到：
使用连接是更有效的方案。它不需要额外的流聚合（stream aggregate），即子查询所需在big_sales.qty列的求和。
UNION vs UNION ALL
无论何时尽可能用UNION ALL 代替UNION。其中的差异是因为UNION有排除重复行并且对结果进行排序的副作用，而UNION ALL不会做这些工作。选择无重复行的结果需要建立临时工作表，用它排序所有行并且在输出之前排序。（在一个select distinct 查询中显示查询计划将发现存在一个流聚合，消耗百分之三十多的资源处理查询）。当你确切知道你得需要时，可以使用UNION。但假如你估计在结果集中没有重复的行，就使用UNION ALL吧。它只是从一个表或一个连接中选择，然后从另一个表中选择，附加在第一条结果集的底部。UNION ALL不需要工作表和排序（除非其它条件引起的）。在大部分情况下UNION ALL更具效率。一个有潜在危险的问题是使用UNION会在数据库中产生巨大的泛滥的临时工作表。假如你期望从UNION查询中获得大量的结果集时，这就可能发生。
示例
下面的查询是选择pubs数据库中的表sales的所有商店的ID，也选择表big_sales中的所有商店的ID，这个表中我们加入了70，000多行数据。在这两个方案间不同之处仅仅是UNION 与UNION ALL的使用比较。但在这个计划中加入ALL关键字产生了三大不同。第一个方案中，在返回结果集给客户端之前需要流聚合并且排序结果。第二个查询更有效率，特别是对大表。在这个例子中两个查询返回同样的结果集，虽然顺序不同。在我们的测试中有两个临时表。你的结果可能会稍有差异。
虽然在这个例子的结果集是可互换的，你可以看到UNION ALL语句比UNION语句少消耗一半的资源。所以应当预料你的结果集并且确定已经没有重复时，使用UNION ALL子句。
函数和表达式约束索引
当你在索引列上使用内置的函数或表达式时，优化器不能使用这些列的索引。尽量重写这些条件，在表达式中不要包含索引列。
示例
你应该帮助SQL Server移除任何在索引数值列周围的表达式。下面的查询是从表jobs通过唯一的聚集索引的唯一键值选择出的一行。假如你在这个列上使用表达式，这个索引就不起作用了。但一旦你将条件’job_id-2=0’ 该成‘job_id=2’，优化器将在聚集索引上执行seek操作。
下面表中列出了多种不同类型查询示例，其被禁止使用列索引，同时给出改写的方法，以获得更优的性能。
SET NOCOUNT ON
使用SET NOCOUNT ON 提高T-SQL代码速度的现象使SQL Server开发者和数据库系统管理者惊讶难解。你可能已经注意到成功的查询返回了关于受影响的行数的系统信息。在很多情况下，你不需要这些信息。这个SET NOCOUNT ON命令允许你禁止所有在你的会话事务中的子查询的信息，直到你发出SET NOCOUNT OFF。
这个选项不只在于其输出的装饰效果。它减少了从服务器端到客户端传递的信息量。因此，它帮助降低了网络通信量并提高了你的事务整体响应时间。传递单个信息的时间可以忽略，但考虑到这种情况，一个脚本在一个循环里执行一些查询并且发送好几千字节无用的信息给用户。
为做个例子，一个文件含T-SQL批处理，其在big_sales表插入了9999行。
-- Assumes the existence of a table called BIG_SALES, a copy of pubs..sales
TOP 和 SET ROWCOUNT
SELECT 语句中的TOP子句限制单个查询返回的行数，而SET ROWCOUNT限制所有后续查询影响的行数。在很多编程任务中这些命令提供了高效率。
SET ROWCOUNT在SELECT，INSERT，UPDATE OR DELETE语句中设置可以被影响的最大行数。这些设置在命令执行时马上生效并且只影响当前的会话。为了移除这个限制执行SET ROWCOUNT 0。
在这部分里我们所有的例子都选择使用Microsoft SHOWPLAN_ALL输出，因为它更紧凑并且展示典型的信息。（Sybase的查询计划基本与此相同，可能包含其它一些信息）大部分的例子都是要么基于PUBS数据库，要么基于标准系统表的。我们在PUBS数据库中对用到的表进行了很大扩充，对很多表增加了好几万行。
子查询优化
一条好的值得称赞的规则是尽量用连接代替所有的子查询。优化器有时可以自动将子查询“扁平化”，并且用常规或外连接代替。但那样也不总是有效。明确的连接对选择表的顺序和找到最可能的计划给出了更多的选项。当你优化一个特殊查询时，了解一下是否去掉自查询可产生很大的差异。
示例
下面查询选择了pubs数据库中所有表的名字，以及每个表的聚集索引（假如存在）。假如没有聚集索引，表名仍然显示在列表中，在聚集索引列中显示为虚线。两个查询返回同样的结果集，但第一个使用了一个子查询，而第二个使用一个外连接时。比较Microsoft SQL Server产生的查询计划：
不必更深探索，我们可以看到在CPU和总的实耗时间方面连接更快，仅需要子查询方案逻辑读的一半。此外，这两种情况伴随着相同的结果集，虽然排序的顺序不同，这是因为连接查询（由于它的GROUP BY子句）有一个隐含的ORDER BY：
查看这个子查询方法展示的查询计划：
反之，求和查询操作我们可以得到：
使用连接是更有效的方案。它不需要额外的流聚合（stream aggregate），即子查询所需在big_sales.qty列的求和。
UNION vs UNION ALL
无论何时尽可能用UNION ALL 代替UNION。其中的差异是因为UNION有排除重复行并且对结果进行排序的副作用，而UNION ALL不会做这些工作。选择无重复行的结果需要建立临时工作表，用它排序所有行并且在输出之前排序。（在一个select distinct 查询中显示查询计划将发现存在一个流聚合，消耗百分之三十多的资源处理查询）。当你确切知道你得需要时，可以使用UNION。但假如你估计在结果集中没有重复的行，就使用UNION ALL吧。它只是从一个表或一个连接中选择，然后从另一个表中选择，附加在第一条结果集的底部。UNION ALL不需要工作表和排序（除非其它条件引起的）。在大部分情况下UNION ALL更具效率。一个有潜在危险的问题是使用UNION会在数据库中产生巨大的泛滥的临时工作表。假如你期望从UNION查询中获得大量的结果集时，这就可能发生。
示例
下面的查询是选择pubs数据库中的表sales的所有商店的ID，也选择表big_sales中的所有商店的ID，这个表中我们加入了70，000多行数据。在这两个方案间不同之处仅仅是UNION 与UNION ALL的使用比较。但在这个计划中加入ALL关键字产生了三大不同。第一个方案中，在返回结果集给客户端之前需要流聚合并且排序结果。第二个查询更有效率，特别是对大表。在这个例子中两个查询返回同样的结果集，虽然顺序不同。在我们的测试中有两个临时表。你的结果可能会稍有差异。
虽然在这个例子的结果集是可互换的，你可以看到UNION ALL语句比UNION语句少消耗一半的资源。所以应当预料你的结果集并且确定已经没有重复时，使用UNION ALL子句。
函数和表达式约束索引
当你在索引列上使用内置的函数或表达式时，优化器不能使用这些列的索引。尽量重写这些条件，在表达式中不要包含索引列。
示例
你应该帮助SQL Server移除任何在索引数值列周围的表达式。下面的查询是从表jobs通过唯一的聚集索引的唯一键值选择出的一行。假如你在这个列上使用表达式，这个索引就不起作用了。但一旦你将条件’job_id-2=0’ 该成‘job_id=2’，优化器将在聚集索引上执行seek操作。
下面表中列出了多种不同类型查询示例，其被禁止使用列索引，同时给出改写的方法，以获得更优的性能。
SET NOCOUNT ON
使用SET NOCOUNT ON 提高T-SQL代码速度的现象使SQL Server开发者和数据库系统管理者惊讶难解。你可能已经注意到成功的查询返回了关于受影响的行数的系统信息。在很多情况下，你不需要这些信息。这个SET NOCOUNT ON命令允许你禁止所有在你的会话事务中的子查询的信息，直到你发出SET NOCOUNT OFF。 这个选项不只在于其输出的装饰效果。它减少了从服务器端到客户端传递的信息量。因此，它帮助降低了网络通信量并提高了你的事务整体响应时间。传递单个信息的时间可以忽略，但考虑到这种情况，一个脚本在一个循环里执行一些查询并且发送好几千字节无用的信息给用户。
为做个例子，一个文件含T-SQL批处理，其在big_sales表插入了9999行。
-- Assumes the existence of a table called BIG_SALES, a copy of pubs..sales
SET NOCOUNT ON
DECLARE @separator VARCHAR(25),
@message VARCHAR(25),
@counter INT,
@ord_nbr VARCHAR(20),
@order_date DATETIME,
@store_nbr INT,
@qty_sold INT,
@terms VARCHAR(12),
@title CHAR(6),
@starttime DATETIME
SET @STARTTIME = GETDATE()
SELECT @counter = 0,
@separator = REPLICATE( '-', 25 )
WHILE @counter < 9999
BEGIN
SET @counter = @counter + 1
SET @ord_nbr = 'Y' + CAST(@counter AS VARCHAR(5))
SET @order_date = DATEADD(hour, (@counter * 8), 'Jan 01 1999')
SET @store_nbr =
CASE WHEN @counter < 999 THEN '6380'
WHEN @counter BETWEEN 1000 AND 2999 THEN '7066'
WHEN @counter BETWEEN 3000 AND 3999 THEN '7067'
WHEN @counter BETWEEN 4000 AND 6999 THEN '7131'
WHEN @counter BETWEEN 7000 AND 7999 THEN '7896'
WHEN @counter BETWEEN 8000 AND 9999 THEN '8042'
ELSE '6380'
END
SET @qty_sold =
CASE WHEN @counter BETWEEN 0 AND 2999 THEN 11
WHEN @counter BETWEEN 3000 AND 5999 THEN 23
ELSE 37
END
SET @terms =
CASE WHEN @counter BETWEEN 0 AND 2999 THEN 'Net 30'
WHEN @counter BETWEEN 3000 AND 5999 THEN 'Net 60'
ELSE 'On Invoice'
END
-- SET @title = (SELECT title_id FROM big_sales WHERE qty = (SELECT MAX(qty)
FROM big_sales))
SET @title =
CASE WHEN @counter < 999 THEN 'MC2222'
WHEN @counter BETWEEN 1000 AND 1999 THEN 'MC2222'
WHEN @counter BETWEEN 2000 AND 3999 THEN 'MC3026'
WHEN @counter BETWEEN 4000 AND 5999 THEN 'PS2106'
WHEN @counter BETWEEN 6000 AND 6999 THEN 'PS7777'
WHEN @counter BETWEEN 7000 AND 7999 THEN 'TC3218'
ELSE 'PS1372'
END
-- PRINT @separator
-- SELECT @message = STR( @counter, 10 ) -- + STR( SQRT( CONVERT( FLOAT,
@counter ) ), 10, 4 )
-- PRINT @message
BEGIN TRAN
INSERT INTO [pubs].[dbo].[big_sales]([stor_id], [ord_num], [ord_date],
[qty], [payterms], [title_id])
VALUES(@store_nbr, CAST(@ord_nbr AS CHAR(5)), @order_date, @qty_sold,
@terms, @title)
COMMIT TRAN
END
SET @message = CAST(DATEDIFF(ms, @starttime, GETDATE()) AS VARCHAR(20))
PRINT @message
/*
TRUNCATE table big_sales
INSERT INTO big_sales
SELECT * FROM sales
SELECT title_id, sum(qty)
FROM big_sales
group by title_id
order by sum(qty)
SELECT * FROM big_sales
*/
|
当带SET NOCOUNT OFF命令运行，实耗时间是5176毫秒。当带SET NOCOUNT ON命令运行，实耗时间是1620毫秒。假如不需要输出中的行数信息，考虑在每一个存储过程和脚本开始时增加SET NOCOUNT ON 命令将。
TOP 和 SET ROWCOUNT
SELECT 语句中的TOP子句限制单个查询返回的行数，而SET ROWCOUNT限制所有后续查询影响的行数。在很多编程任务中这些命令提供了高效率。
SET ROWCOUNT在SELECT，INSERT，UPDATE OR DELETE语句中设置可以被影响的最大行数。这些设置在命令执行时马上生效并且只影响当前的会话。为了移除这个限制执行SET ROWCOUNT 0。
一些实际的任务用TOP or SET ROWCOUNT比用标准的SQL命令对编程是更有效率的。让我们在几个例子中证明：
TOP n
在几乎所有的数据库中最流行的一个查询是请求一个列表中的前N项。在 pubs数据库案例中，我们可以查找销售最好CD的前五项。比较用TOP，SET ROWCOUNT和使用ANSI SQL的三种方案。
纯 ANSI SQL：
Select title,ytd_sales
From titles a
Where (select count(*)
From titles b
Where b.ytd_sales>a.ytd_sales
)<5
Order by ytd_sales DESC
|
这个纯ANSI SQL方案执行一个效率可能很低的关联子查询，特别的在这个例子中，在ytd_sales上没有索引支持。另外，这个纯的标准SQL命令没有过滤掉在ytd_sales的空值,也没有区别多个CD间有关联的情况。
使用 SET ROWCOUNT：
SET ROWCOUNT 5
SELECT title, ytd_sales
FROM titles
ORDER BY ytd_sales DESC
SET ROWCOUNT 0
|
使用 TOP n：
SELECT TOP 5 title, ytd_sales
FROM titles
ORDER BY ytd_sales DESC
|
第二个方案使用SET ROWCOUNT来停止SELECT查询，而第三个方案是当它找到前五行时用TOP n来停止。在这种情况下，在获得结果之前我们也要有一个ORDER BY子句强制对整个表进行排序。两个查询的查询计划实际上是一样的。然而，TOP优于SET ROWCOUNT的关键点是SET必须处理ORDER BY子句所需的工作表，而TOP 不用。
在一个大表上，我们可以在ytd_sales上创建一个索引以避免排序。查询将使用该索引找到前5行并停止。与第一个方案相比较，其扫描了整个表，并对每一行执行了一个关联子查询。在小表上，性能的差异是很小的。但是在一个大表上，第一个方案的处理时间可能是数个小时，而后两个方法是数秒。
当确定查询需要时，请考虑是否只需要其中几行，假如是，使用TOP子句将节约大量时间。
声明： 此文观点不代表本站立场；转载务必保留本文链接；版权疑问请联系我们。
|
