SQL Performance, Using OPTION (FAST n)

Question

Can anyone tell me what's the disadvantages of using OPTION (FAST n) in SQL Queries.

For example, I grab 100,000 records so quickly, but does this make effect on other processes of SQL Server?

---

I am moving a bit close to my issue.

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.

Any idea how can I do this.

Thanks

Answer 1

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.

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.

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)