December 11, 2007 at 5:01 am
It's briefly mention in this article and appears to return the first row of a result set to the user/application without waiting for the entire query to complete.
http://qa.sqlservercentral.com/articles/Performance+Tuning/tablehints/667/
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 11, 2007 at 5:58 am
It can be useful if you're returning a large amount of data and you don't want to wait for all the rows to process prior to the data returning. You take a query like this:
SELECT *
FROM [Sales].[SalesOrderDetail] sod
JOIN [Sales].[SalesOrderHeader] soh
ON [sod].[SalesOrderID] = [soh].[SalesOrderID]
And run it against Adventureworks. It uses a HASH MATCH to return the data. But add the FAST n hint and it substitutes the hash with a LOOP. The overall query runs slower, but you get the first row out of it much quicker.
BTW, MS recommends using 'FAST n' instead of FASTFIRSTROW because they may be planning to deprecate it. For what its worth. Effectively, there's no real difference in behavior.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 11, 2007 at 6:50 am
but my question being that UserID will get only one row as it is the primary key. In this case what advantage is FASTFIRSTROW?
December 11, 2007 at 6:53 am
You will not get an advantage when returning one row. This Hint is used for large (time intensive) result sets.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 11, 2007 at 6:59 am
What Jason said.
But remember, it's going to force the optimizer to make other choices. Every so often those other choices might work better, but then it's because the HASH join would be better than the LOOP or vice versa, not because FAST n worked. The answer is always to look first to your execution plan to figure out if you need to possibly force it in a new direction (usually a very poor choice).
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 12, 2007 at 3:32 am
just be wary of using the fast hint as it can create less than optimal cached plans that can really mess performance ( just ask navision users ! ) You'll find top is a better way to go although it's not the same, just be very careful, you may need to add recompile hints to your queries that use the fast hint.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 12, 2007 at 3:46 am
but please tell me why do i need to add a recompile hint ??? Cause if i understand right Recompile is added when i want the cached execution plan to be invalidated and generate a new execution plan
Now in case of FASTFIRSTROW..I am trying to achieve a fast response time ... what it would do is select an index which will give the first row in minimum time ................
How do i correlate the two?
December 12, 2007 at 5:37 am
You're operating under a slight misconception. FAST 'n' (or FASTFIRSTROW) doesn't give you faster response time. It gives you slower response time with the appearance of faster responses. The hint forces the optimizer to choose a plan that is returns the beginning of the result set as fast as possible, but at the sacrifice in speed for the entire result set. This is only useful within client applications that are returning larger result sets, but you want to get a row or three on the screen as soon as you can and don't mind that the other umpty-ump-million rows take longer to process.
That's why, to return just a single row, it's completely a waste of time. You would be, in fact, hurting performance for no benefit whatsoever.
As to why you'd specifically need RECOMPILE in addition to FAST 'n', I'm curious too. I wouldn't think this plan would be any less likely to be retained and reused because of the FAST 'n' hint.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 12, 2007 at 5:57 am
Grant Fritchey (12/12/2007)
This is only useful within client applications that are returning larger result sets, but you want to get a row or three on the screen as soon as you can and don't mind that the other umpty-ump-million rows take longer to process.
The scenario being that I use the hint within the stored proc.
Declare @tempUserID uniqueidentifier
--- Some queries
SELECT
@tempUserID = UserID
FROM
User WITH (FASTFIRSTROW)
--- Some more queries
In this case the @tempUserID is being used in the next few queries. In this case there will be not even the seemingly less response time Am I right? Cause ultimately it is the final value of the @tempUserId which will be used. Does this make sense ???? 🙂 looks like to me 🙂
December 12, 2007 at 6:11 am
Megha Yadav (12/12/2007)[hr
In this case the @tempUserID is being used in the next few queries. In this case there will be not even the seemingly less response time Am I right? Cause ultimately it is the final value of the @tempUserId which will be used. Does this make sense ???? 🙂 looks like to me 🙂
You shouldn't be using fastfirstrow in that case. You can get slower performance with it than without. Fast first row is used to give the impression of fast result return, when the resultset is large and may take some time to return completely.
In your case, use top and leave the query hints out.
SELECT TOP 1 @tempUserID = UserID
FROM User
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 12, 2007 at 6:22 am
No. This wouldn't be helpful to you at all. In fact, it would probably hurt performance.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 13, 2007 at 10:08 am
Do not use FASTFIRSTROW hint on a Primary Key access.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 13, 2007 at 10:35 am
TheSQLGuru (12/13/2007)
Do not use FASTFIRSTROW hint on a Primary Key access.
But why do u say so ????
December 13, 2007 at 10:55 am
Megha Yadav (12/13/2007)
TheSQLGuru (12/13/2007)
Do not use FASTFIRSTROW hint on a Primary Key access.But why do u say so ????
Because it's not necessary. If you're filtering with an equality on the primary key column, you will only get one row back. As has been stated several times, FASTFIRSTROW is for large recordsets where you want the first few rows to return to the client quickly, to give the appearance of fast results.
I'll go further that TheSQLGuru in fact. Don't use FASTFIRSTROW.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply