Option (FAST 1000) 1

[wg26]

Hi:

I am trying to use this clause 'Option (FAST 1000)' to quick return first 1000 raw found so that make query seemed run faster. But the command does not seem working. The query still return all the data found all at once. Does anyone know why? and under what conditions the command will work? Thanks alot..

 

[foxdev]

The query using OPTION (FAST n) is designed to return all rows, but optimize the query so that the first n rows are returned in the shortest amount of time.

 

[wg26]

Hi:

I have read many performance tips about that 'You can quickly get the n rows and can work with them, when the query continues execution and produces its full result set'. Now I am trying to use this by using Option(fast, n)...but it doesn't seem working, Can anyone please look at my query and see if I use it right, if not, where and how I should use it...Thanks alot..

SELECT wp.[Unique ID], wp.[Tool ID],
wp.[Chamber Number], wp.[Chamber ID],
wp.[Lot ID], wp.[Substrate ID],
wp.[Substrate Start Time], wp.[Recipe Lookup ID],
rp.[Sensor Number],rp.[Sensor Type],
rp.[File PathName],rp.[Run Time],
ra.[Analysis When],ra.[Analysis Type],
ra.[Analysis Step],ra.[ReAnal Time],
ra.[ReAnal Recipe File PathName],rad.[Detail Name],
rad.[Detail NumData],rad.[Detail Min],
rad.[Detail Max], rad.[Detail Avg],
rad.[Detail SDev]

FROM [Wafer Parameters] AS wp INNER JOIN
( [Run Parameters] AS rp INNER JOIN
( [Run Analyses] AS ra INNER JOIN
[Run Analysis Detail] AS rad
ON ra.[PK Run Analyses] = rad.[FK Run Analyses])
ON rp.[PK Run Parameters] = ra.[FK Run Parameters])
ON wp.[PK Wafer Parameters] = rp.[FK Wafer Parameters]

WHERE wp.[Substrate Start Time] Between '1/1/1993 0:0:0' and '5/16/2002 17:20:00'
And rad.[Detail Name] = '18 amu'

ORDER BY rad.[Detail Name],
ra.[Analysis Step],
ra.[Analysis Type],
ra.[ReAnal Time],
rp.[Sensor Type],
wp.[Substrate Start Time],
wp.[Lot ID],
wp.[Tool ID],
wp.[Chamber ID]
Option (Fast 10000)

 

[tlbroadbent]

I've never had occasion to use the Fast N option so my comments are not based on experience.

First, I have a question. How are you executing the query - Query Analyzer or other software?

I've read that Fast N is only useful if SQL can use indexes to select data in the order specified in the Order By clause. In your query, that's not likely because the Order By columns are from three tables.

When you do an Order By in the query, if SQL Server cannot use indexes to retrieve ordered rows, it must retrieve and then sort the entire result set before any rows can be returned. Fast N won't provide much, if any, performance boost in this case.

I think Fast N would be useful when returning the first 10 to 100 rows rather than 10000. Have you tried a smaller number? How many rows are returned in the entire result set?


Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[wg26]

Thanks Terry.

I think that what you said totally made sense. I am using Analyzer to test if it works. But I guess it does not help in my case. I did try with smaller number but as you said, SQL still has to sort whole set before it can return anything. So when it finishes sorting, it just returns whole set all together. My returning record set is about 40,000 rows. I guess I will not use this option in my query. Again,Thanks for your help. It really solved my puzzle.