問題描述
誰能告訴我在 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)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！