问题描述
谁能告诉我在 SQL 查询中使用 OPTION (FAST n) 的缺点是什么.
Can anyone tell me what's the disadvantages of using OPTION (FAST n) in SQL Queries.
比如我这么快就抓取了10万条记录，但是这对SQL Server的其他进程有影响吗?
For example, I grab 100,000 records so quickly, but does this make effect on other processes of SQL Server?
我有点接近我的问题.
我必须每周运行一次数据处理.所以第一个结果在 5-7 秒后出来，然后我对这些结果进行数据处理.结果通常由几千行组成.每一行都需要几秒钟的时间来处理.通常，该过程会等待整个结果出现，然后开始处理.结果出现在数据集中(我正在使用 c# 控制台应用程序)，所以我希望前 10 个结果快速出现，以便我可以立即开始该过程，然后其余的行出现并添加到队列中并等那里转.
I have to run a data process every week. So the first result comes out after 5-7 seconds and then I do my data process on these results. The results normally consists of few thousand rows. and every row take a few seconds to be processed. Normally the process waits for the whole result to be there then it start processing. The result comes out in dataset (I am using c# console app), I So I want the top 10 results to comes out quickly so that I can start the process immediately and then the rest of the rows comes out and add in the queue and wait for there turn.
知道我该怎么做.
谢谢
推荐答案
Option fast 强制查询优化器不优化查询的总运行时间，而是优化获取前 N 行所需的时间.
Option fast forces the query optimizer to not optimize the total runtime of the query, but the time it takes to fetch the first N rows.
如果您有 2 个 100 万行的表要加入，标准查询计划是一个表(一百万行的临时表)的哈希图，然后在另一个表上使用哈希图查找.
if you have 2 tables of 1 million rows you want to join, a standard query plan is a hashmap of one table (temp table of a million rows) and then use a hashmap lookup on the other.
快速 10 优化可能只使用嵌套循环，因为构建 100 万行哈希图的工作量比嵌套循环的快速 10 步骤要多得多.如果您毕竟有 100 万行，则嵌套循环可能需要多花 3 倍的时间，但在快速 10 行下，您会更快地获得这 10 行.(这个例子假设存在一个合适的索引)
a fast 10 optimisation would probably just use nested loops, because the effort of building that 1 million row hashmap is quite a bit more than the fast 10 steps of nested loop. If you are after all 1 million rows, the nested loop could take 3 times longer, but under fast 10, you'll get those 10 quicker. (this example assumes the existence of a suitable index)
这篇关于SQL 性能，使用选项 (FAST n)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！