Share via

Row Goals revisited - FAST hint guidance

  • Article

A question came in about when to use the FAST hint.

If you remember from the row goals post (https://blogs.msdn.com/controlpanel/blogs/posteditor.aspx?SelectedNavItem=Posts&sectionid=6255&postid=564912), it is possible to get a different plan based on the row goal (for example, you can get a nested loops plan instead of a merge/hash join if you have a low enough row goal).  TOP is a common reason to get a row goal in the optimization process.  TOP queries can also be used to page through a result set using a series of queries (if you remember the last row you saw + a few other conditions).

The FAST hint is a way to get the plan you would get with TOP but without the hard limit on the number of rows being returned.  So, if you want to get one "page" of rows quickly, you can use FAST to enable that.  If you want the ability to continue reading from the result set, you can use the FAST hint to get that "give me rows quickly" plan. 

Typically, ISVs that do paging themselves without server-side cursors may consider this hint.  In general, I wouldn't recommend that you use it unless you have a good reason to do so.  If you can ignore it, as with all hints, please do so.  If you have really specific requirements on how your application should work, then you can consider it as a technique that picks more loops joins and minimizes stop-and-go operators, where possible.

Thanks,

Conor

Comments