問(wèn)題描述
誰(shuí)能告訴我在 SQL 查詢(xún)中使用 OPTION (FAST n) 的缺點(diǎn)是什么.
Can anyone tell me what's the disadvantages of using OPTION (FAST n) in SQL Queries.
比如我這么快就抓取了10萬(wàn)條記錄，但是這對(duì)SQL Server的其他進(jìn)程有影響嗎?
For example, I grab 100,000 records so quickly, but does this make effect on other processes of SQL Server?
我有點(diǎn)接近我的問(wèn)題.
我必須每周運(yùn)行一次數(shù)據(jù)處理.所以第一個(gè)結(jié)果在 5-7 秒后出來(lái)，然后我對(duì)這些結(jié)果進(jìn)行數(shù)據(jù)處理.結(jié)果通常由幾千行組成.每一行都需要幾秒鐘的時(shí)間來(lái)處理.通常，該過(guò)程會(huì)等待整個(gè)結(jié)果出現(xiàn)，然后開(kāi)始處理.結(jié)果出現(xiàn)在數(shù)據(jù)集中(我正在使用 c# 控制臺(tái)應(yīng)用程序)，所以我希望前 10 個(gè)結(jié)果快速出現(xiàn)，以便我可以立即開(kāi)始該過(guò)程，然后其余的行出現(xiàn)并添加到隊(duì)列中并等那里轉(zhuǎn).
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 強(qiáng)制查詢(xún)優(yōu)化器不優(yōu)化查詢(xún)的總運(yùn)行時(shí)間，而是優(yōu)化獲取前 N 行所需的時(shí)間.
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 個(gè) 100 萬(wàn)行的表要加入，標(biāo)準(zhǔn)查詢(xún)計(jì)劃是一個(gè)表(一百萬(wàn)行的臨時(shí)表)的哈希圖，然后在另一個(gè)表上使用哈希圖查找.
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 優(yōu)化可能只使用嵌套循環(huán)，因?yàn)闃?gòu)建 100 萬(wàn)行哈希圖的工作量比嵌套循環(huán)的快速 10 步驟要多得多.如果您畢竟有 100 萬(wàn)行，則嵌套循環(huán)可能需要多花 3 倍的時(shí)間，但在快速 10 行下，您會(huì)更快地獲得這 10 行.(這個(gè)例子假設(shè)存在一個(gè)合適的索引)
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)
這篇關(guān)于SQL 性能，使用選項(xiàng) (FAST n)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！