March 13, 2012 at 7:11 am
I run the 2 quries :-
1. select * from college_master where college_clienttype='Paid' order by college_name
2. select * from college_master where college_clienttype='Paid' order by college_name option(fast 1000)
I read Option(fast n) executes query fast and gives o/p soon.
but after the observation of both query's execution time... i didn't find any effect on execution.....
If you have any idea of it please guide me.
Thanks & Regards,
Pallavi
March 13, 2012 at 7:17 am
pallavi.unde (3/13/2012)
...
I read Option(fast n) executes query fast and gives o/p soon.
...
Where have you read this from?
It does not make query to run any faster! It just makes query to return required number of rows (n) immediately as they were found.
March 13, 2012 at 8:50 am
Eugene Elutin (3/13/2012)
pallavi.unde (3/13/2012)
...
I read Option(fast n) executes query fast and gives o/p soon.
...
Where have you read this from?
It does not make query to run any faster! It just makes query to return required number of rows (n) immediately as they were found.
It's not quite either of those things. It just makes it favour plans that may return partial result sets in a shorter amount of time. So it may pick something like a nested loops join rather than a hash join as it takes longer for the whole batch, but it can process rows as they come through rather than having to build hash tables for the whole result set up front.
SQL Server will always output rows that it's already processed as soon as it has them unless there's something like a sort that requires it to collect the whole result set before presenting results.
It's entirely possible for this optimisation to produce the same plan as it would have without the hint (e.g. it already is using the fastest plan possible for the first n rows).
March 13, 2012 at 9:17 am
HowardW (3/13/2012)
Eugene Elutin (3/13/2012)
pallavi.unde (3/13/2012)
...
I read Option(fast n) executes query fast and gives o/p soon.
...
Where have you read this from?
It does not make query to run any faster! It just makes query to return required number of rows (n) immediately as they were found.
It's not quite either of those things. It just makes it favour plans that may return partial result sets in a shorter amount of time. So it may pick something like a nested loops join rather than a hash join as it takes longer for the whole batch, but it can process rows as they come through rather than having to build hash tables for the whole result set up front.
SQL Server will always output rows that it's already processed as soon as it has them unless there's something like a sort that requires it to collect the whole result set before presenting results.
It's entirely possible for this optimisation to produce the same plan as it would have without the hint (e.g. it already is using the fastest plan possible for the first n rows).
From http://msdn.microsoft.com/en-us/library/ms181714.aspx
FAST number_rows
Specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set
Cannot see much difference to what I've said
March 13, 2012 at 9:29 am
The BOL article is not saying the same thing as you did.
All queries return results as soon as they are found, this hint doesn't change that behaviour at all. All it does is potentially change the plan so that it can return a partial resultset sooner, at the expense of overall execution time.
March 13, 2012 at 9:46 am
what the following sentence from this article does mean than? :
After the first number_rows are returned, the query continues execution and produces its full result set
It may be possible to explain it in more complicated form, but the above wording is exactly what MS decided to use...
March 13, 2012 at 10:08 am
I'm not disputing what the BOL article says, it's correct. However it's still different to what you said, hence my clarification.
All the sentence you've quoted is saying is that it'll still get the full resultset after it's returned the first n rows (as does every other query ;-))
March 13, 2012 at 10:13 am
ok, let's correct my words:
It does not make query to run any faster! It just makes query to return required number of rows (n) immediately as they were found.
The rest of rows (if any more can be returned by the query) will follow...
:hehe:
March 13, 2012 at 10:24 am
Eugene Elutin (3/13/2012)
ok, let's correct my words:It does not make query to run any faster! It just makes query to return required number of rows (n) immediately as they were found.
The rest of rows (if any more can be returned by the query) will follow...
:hehe:
The bit I've highlighted is what I was correcting. Why would the default option not be to return rows immediately as they are found? The only thing potentially changed by the hint is the plan it uses as it'll favour plans that can start completing rows up front (assuming that it estimates that the n rows will be produced quicker than they would be with the overall best plan).
March 13, 2012 at 10:50 am
Ok, let's settle this. Its not about returning found rows faster or immediately. Its about FINDING the n rows faster (possibly), which as a direct result will return them faster.
Jared
CE - Microsoft
March 13, 2012 at 11:00 am
SQLKnowItAll (3/13/2012)
Ok, let's settle this. Its not about returning found rows faster or immediately. Its about FINDING the n rows faster (possibly), which as a direct result will return them faster.
No! Cannot see the road for settlement here!:angry:
It's not "about FINDING the n rows faster (possibly)"
It's about attempt to produce the query plan optimised for "FINDING the n rows faster (possibly)"
:hehe::hehe::hehe::hehe:
March 13, 2012 at 11:04 am
Eugene Elutin (3/13/2012)
SQLKnowItAll (3/13/2012)
Ok, let's settle this. Its not about returning found rows faster or immediately. Its about FINDING the n rows faster (possibly), which as a direct result will return them faster.No! Cannot see the road for settlement here!:angry:
It's not "about FINDING the n rows faster (possibly)"
It's about attempt to produce the query plan optimised for "FINDING the n rows faster (possibly)"
:hehe::hehe::hehe::hehe:
Oh come on... That's the same thing. :hehe: The point is, that rows are always returned immediately as they are "found." The hint makes it find them faster (possibly) by optimizing the execution plan to do that (if it can). I think we are all saying the same things in different ways, depending on how you interpret it lol
Jared
CE - Microsoft
March 13, 2012 at 11:05 am
Okay, please stop. i don't think this discussion that really comes down to semantics is really helping. All I really am seeing now is each person trying to defend their own position when really, if you think about what is happening, everyone is saying basically the same thing.
March 13, 2012 at 11:08 am
basically the same thing.
That what I've thought from the beginning...
Look's like nothing better to discuss on Tuesday evening :hehe:
March 13, 2012 at 11:10 am
Well, I'd like to turn the topic to why this would be used. Would I do a top 100 with a FAST 100? It seems needless to want to return 100 or 1000 rows before others since you have to wait for the query to finish before really doing anything anyway. Anyone have a business case for this?
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply