SQL 性能，使用选项 (FAST n)

问题描述

谁能告诉我在 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)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！