I was reading over the documentation for query hints: http://msdn.microsoft.com/en-us/library/ms181714(SQL.90).aspx
我正在阅读查询提示的文档:http://msdn.microsoft.com/en-us/library/ms181714(SQL.90).aspx
And noticed this: FAST number_rows Specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set.
并注意到这一点:FAST number_rows指定优化查询以快速检索第一个number_rows。这是一个非负整数。返回第一个number_rows后,查询继续执行并生成其完整结果集。
So when I'm doing a query like:
所以,当我正在进行如下查询:
Select Name from Students where ID = 444
Should I bother with a hint like this? Assuming SQL Server 2005, when should I?
我应该打扰这样的提示吗?假设SQL Server 2005,我应该什么时候?
-- edit --
- 编辑 -
Also should one bother when limiting results:
在限制结果时也应该打扰一下:
Select top 10 * from Students OPTION (FAST 10)
3 个解决方案
#1
2
When using TOP x, there's no benefit of also using OPTION FAST x. The query optimizer already makes its decisions based on how many rows you are retrieving. Same goes for trivial queries, such as querying for a particular value from a unique index.
使用TOP x时,使用OPTION FAST x也没有任何好处。查询优化器已根据您要检索的行数做出决策。对于简单的查询也是如此,例如从唯一索引查询特定值。
Other than that, OPTION FAST x could help when you know the number of results is likely below x, but the query optimizer does not. Of course, if the query optimizer is choosing poor paths for complex queries with few results, your statistics may need to be updated. And if you guess wrong on x, the query may end up taking longer--almost always a risk when giving hints.
除此之外,OPTION FAST x可以帮助您知道结果数量可能低于x,但查询优化器不会。当然,如果查询优化器为复杂查询选择较差的路径且结果很少,则可能需要更新统计信息。如果您在x上猜错了,那么查询可能会花费更长的时间 - 在给出提示时几乎总是存在风险。
The above statement has not been tested--it may be that all queries take just as long to fully execute, if not longer. Getting the first 10 rows fast is great if there are only 8 rows, but theoretically the query still has to execute fully before finishing. The benefit I'm thinking may be there because the query execution takes a different path expecting fewer total records, when in fact it's really trying to get the first x faster. Those two types of optimizations may not be in alignment.
上述声明尚未经过测试 - 可能所有查询都需要完全执行,如果不是更长时间。如果只有8行,快速获得前10行是很好的,但理论上,查询仍然必须在完成之前完全执行。我正在考虑的好处可能在那里,因为查询执行采用不同的路径期望更少的总记录,而事实上它确实试图让第一个x更快。这两种类型的优化可能不一致。
#2
18
The FAST hint only makes sense on complex queries where there are multiple alternatives the optimizer could choose from. For a simple query like your example it doesn't help with anything, the query optimizer will immediately determine that there is a trivial plan (seek in ID index, lookup Name if not covering) to satisfy the query and go for it. Even if no index exists on ID, the plan is still trivial (probably clustered scan).
FAST提示仅适用于复杂查询,其中优化程序可以选择多种替代方法。对于像您的示例这样的简单查询,它对任何事情没有帮助,查询优化器将立即确定存在一个简单的计划(在ID索引中查找,如果没有覆盖则查找名称)以满足查询并去寻找它。即使ID上没有索引,该计划仍然是微不足道的(可能是集群扫描)。
To give an example where FAST would be useful consider a join between A and B, with an ORDER BY constraint. Say evaluating the join B first and nested loops A honors the ORDER BY constraint, so will produce fast results (no SORT necessary), but is more costly because of cardinality (B has many records that match the WHERE, while A has few). On the other hand evaluating B first and nested loop A would produce a query that does less IO hence is faster overall, but the result would have to be sorted first and SORT can only start after the join is evaluated, so the first result will come very late. The optimizer would normally pick the second plan because is more efficient overall. The FAST hint would cause the optimizer to pick the first plan, because it produces results faster.
举一个FAST有用的例子,考虑A和B之间的连接,并使用ORDER BY约束。首先评估连接B和嵌套循环A遵循ORDER BY约束,因此将产生快速结果(不需要SORT),但由于基数(B有许多与WHERE匹配的记录,而A很少),因此成本更高。另一方面,首先评估B和嵌套循环A会产生一个查询,它会减少IO,因此总体上更快,但结果必须先排序,SORT只能在评估连接后开始,所以第一个结果将会到来很晚了。优化器通常会选择第二个计划,因为整体效率更高。 FAST提示会导致优化器选择第一个计划,因为它可以更快地生成结果。
#3
1
For that particular query, certainly not! It's only going to return one row — the row with ID = 444. SQL Server will select that row as efficiently as it can.
对于那个特定的查询,当然不是!它只返回一行--ID = 444的行.SQL Server将尽可能有效地选择该行。
FAST 10 might be used in a situation where you could make use of the first 10 rows immediately, even as you continue to wait for further results.
FAST 10可能会在您可以立即使用前10行的情况下使用,即使您继续等待进一步的结果。
#1
2
When using TOP x, there's no benefit of also using OPTION FAST x. The query optimizer already makes its decisions based on how many rows you are retrieving. Same goes for trivial queries, such as querying for a particular value from a unique index.
使用TOP x时,使用OPTION FAST x也没有任何好处。查询优化器已根据您要检索的行数做出决策。对于简单的查询也是如此,例如从唯一索引查询特定值。
Other than that, OPTION FAST x could help when you know the number of results is likely below x, but the query optimizer does not. Of course, if the query optimizer is choosing poor paths for complex queries with few results, your statistics may need to be updated. And if you guess wrong on x, the query may end up taking longer--almost always a risk when giving hints.
除此之外,OPTION FAST x可以帮助您知道结果数量可能低于x,但查询优化器不会。当然,如果查询优化器为复杂查询选择较差的路径且结果很少,则可能需要更新统计信息。如果您在x上猜错了,那么查询可能会花费更长的时间 - 在给出提示时几乎总是存在风险。
The above statement has not been tested--it may be that all queries take just as long to fully execute, if not longer. Getting the first 10 rows fast is great if there are only 8 rows, but theoretically the query still has to execute fully before finishing. The benefit I'm thinking may be there because the query execution takes a different path expecting fewer total records, when in fact it's really trying to get the first x faster. Those two types of optimizations may not be in alignment.
上述声明尚未经过测试 - 可能所有查询都需要完全执行,如果不是更长时间。如果只有8行,快速获得前10行是很好的,但理论上,查询仍然必须在完成之前完全执行。我正在考虑的好处可能在那里,因为查询执行采用不同的路径期望更少的总记录,而事实上它确实试图让第一个x更快。这两种类型的优化可能不一致。
#2
18
The FAST hint only makes sense on complex queries where there are multiple alternatives the optimizer could choose from. For a simple query like your example it doesn't help with anything, the query optimizer will immediately determine that there is a trivial plan (seek in ID index, lookup Name if not covering) to satisfy the query and go for it. Even if no index exists on ID, the plan is still trivial (probably clustered scan).
FAST提示仅适用于复杂查询,其中优化程序可以选择多种替代方法。对于像您的示例这样的简单查询,它对任何事情没有帮助,查询优化器将立即确定存在一个简单的计划(在ID索引中查找,如果没有覆盖则查找名称)以满足查询并去寻找它。即使ID上没有索引,该计划仍然是微不足道的(可能是集群扫描)。
To give an example where FAST would be useful consider a join between A and B, with an ORDER BY constraint. Say evaluating the join B first and nested loops A honors the ORDER BY constraint, so will produce fast results (no SORT necessary), but is more costly because of cardinality (B has many records that match the WHERE, while A has few). On the other hand evaluating B first and nested loop A would produce a query that does less IO hence is faster overall, but the result would have to be sorted first and SORT can only start after the join is evaluated, so the first result will come very late. The optimizer would normally pick the second plan because is more efficient overall. The FAST hint would cause the optimizer to pick the first plan, because it produces results faster.
举一个FAST有用的例子,考虑A和B之间的连接,并使用ORDER BY约束。首先评估连接B和嵌套循环A遵循ORDER BY约束,因此将产生快速结果(不需要SORT),但由于基数(B有许多与WHERE匹配的记录,而A很少),因此成本更高。另一方面,首先评估B和嵌套循环A会产生一个查询,它会减少IO,因此总体上更快,但结果必须先排序,SORT只能在评估连接后开始,所以第一个结果将会到来很晚了。优化器通常会选择第二个计划,因为整体效率更高。 FAST提示会导致优化器选择第一个计划,因为它可以更快地生成结果。
#3
1
For that particular query, certainly not! It's only going to return one row — the row with ID = 444. SQL Server will select that row as efficiently as it can.
对于那个特定的查询,当然不是!它只返回一行--ID = 444的行.SQL Server将尽可能有效地选择该行。
FAST 10 might be used in a situation where you could make use of the first 10 rows immediately, even as you continue to wait for further results.
FAST 10可能会在您可以立即使用前10行的情况下使用,即使您继续等待进一步的结果。