我应该使用查询提示快速 number_rows / FASTFIRSTROW 吗？
我正在阅读查询提示的文档： http://msdn.microsoft.com/en-us/ library/ms181714(SQL.90).aspx
并注意到这一点： 快速 number_rows 指定优化查询以快速检索第一个 number_rows。这是一个非负整数。返回第一个 number_rows 后，查询继续执行并生成完整结果集。
因此，当我进行如下查询时：
Select Name from Students where ID = 444
我应该为这样的提示烦恼吗？假设使用 SQL Server 2005，我什么时候应该使用？
-- 编辑 --
限制结果时还应该麻烦一下：
Select top 10 * from Students OPTION (FAST 10)
I was reading over the documentation for query hints:
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.
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?
-- edit --
Also should one bother when limiting results:
Select top 10 * from Students OPTION (FAST 10)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
FAST 提示仅对优化器可以选择多种替代方案的复杂查询才有意义。对于像您的示例这样的简单查询，它没有任何帮助，查询优化器将立即确定存在一个简单的计划（在 ID 索引中查找，如果没有覆盖则查找名称）来满足查询并执行它。即使 ID 上不存在索引，该计划仍然很简单（可能是集群扫描）。
举一个 FAST 有用的例子，考虑 A 和 B 之间的连接，并带有 ORDER BY 约束。假设首先评估连接 B，嵌套循环 A 遵循 ORDER BY 约束，因此会产生快速结果（不需要 SORT），但由于基数而成本更高（B 有许多与 WHERE 匹配的记录，而 A 很少）。另一方面，首先评估 B 和嵌套循环 A 会产生一个执行较少 IO 的查询，因此总体速度更快，但必须首先对结果进行排序，并且 SORT 只能在评估联接之后开始，所以第一个结果会很晚才出现。优化器通常会选择第二个计划，因为整体效率更高。 FAST 提示将导致优化器选择第一个计划，因为它产生结果的速度更快。
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).
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.
使用 TOP x 时，同时使用 OPTION FAST x 没有任何好处。查询优化器已经根据您要检索的行数做出决策。对于普通查询也是如此，例如从唯一索引查询特定值。
除此之外，当您知道结果数量可能低于x时，OPTION FAST x 可能会有所帮助，但查询优化器却不会。当然，如果查询优化器为结果很少的复杂查询选择较差的路径，则您的统计信息可能需要更新。如果您猜错了x，查询最终可能会花费更长的时间——在给出提示时几乎总是存在风险。
上述语句尚未经过测试 - 可能所有查询都需要同样的时间才能完全执行，甚至更长。如果只有 8 行，那么快速获取前 10 行固然很好，但理论上查询仍然必须在完成之前完全执行。我认为的好处可能是存在的，因为查询执行采用不同的路径期望更少的总记录，而实际上它确实试图获取第一个x更快。这两种类型的优化可能不一致。
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.
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.
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.
对于那个特定的查询，当然不是！它只会返回一行 - ID = 444 的行。 SQL Server 将尽可能高效地选择该行。
FAST 10可能用于您可以立即使用前 10 行的情况，即使您继续等待进一步的结果也是如此。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.FAST 10might 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.