locked
FASTFIRSTROW RRS feed

  • Question

  • What exactly is FASTFIRSTROW and when / why is it typically used?
    Tuesday, May 19, 2009 11:53 PM

Answers

  • FASTFIRSTROW is a hint used to instruct the query optimizer to prefer certain query plans.

    SQL Server does not necessarily have to determine the entire result set prior to beginning to return matching rows.  SQL Server can return data as it is read from the database with the caveat that certain operations work against this.  This article (http://sqlblog.com/blogs/kalen_delaney/archive/2008/12/02/using-a-nonclustered-index-to-avoid-a-sort.aspx) shows an example where FASTFIRSTROW avoids doing a sort.  This results in costlier query but allows data to begin streaming to the user earlier.

    FASTFIRSTROW can benefit some user interface scenarios such as a list box with a scroll bar where filling the on-screen "page" of the list box quickly is more important than getting all of the results as quickly as possible.  That being said, I do not think that this feature is used much in practice.

    Note that it is equivalent to the hint OPTION (FAST 1).  The OPTION (FAST 1) is preferred as the FASTFIRSTROW syntax is deprecated.
    Wednesday, May 20, 2009 12:49 AM
  • To add to what BinaryCoder said...

    Look at the Northwind database...

    SELECT OrderID,CustomerID,OrderDate
    FROM Orders
    ORDER BY OrderDate

    There's an index on OrderDate, so you'd think that SQL would use that index to retrieve the orders to ORDER BY that field.

    But if you look at the query plan for that query, SQL instead actually does a scan on the table and SORTs the results.

    If you add a OPTION (FAST 1) to the query, then it WILL use the OrderDate index in order to get the first record out as soon as possible, but the query as a whole will most likely be slower, because, for each entry in the OrderDate index it finds, it has to do a lookup into the clustered index of the table to retrieve the CustomerID.

    This is why the original query plan used a SORT instead... SQL figured it was worth the cost of doing the SCAN/SORT as opposed to all those lookups for each row.
    --Brad
    Wednesday, May 20, 2009 1:20 AM
  • A couple more references from the SQL Server Optimization Team for the truly interested reader:



    Wednesday, May 20, 2009 5:19 AM

All replies

  • FASTFIRSTROW is a hint used to instruct the query optimizer to prefer certain query plans.

    SQL Server does not necessarily have to determine the entire result set prior to beginning to return matching rows.  SQL Server can return data as it is read from the database with the caveat that certain operations work against this.  This article (http://sqlblog.com/blogs/kalen_delaney/archive/2008/12/02/using-a-nonclustered-index-to-avoid-a-sort.aspx) shows an example where FASTFIRSTROW avoids doing a sort.  This results in costlier query but allows data to begin streaming to the user earlier.

    FASTFIRSTROW can benefit some user interface scenarios such as a list box with a scroll bar where filling the on-screen "page" of the list box quickly is more important than getting all of the results as quickly as possible.  That being said, I do not think that this feature is used much in practice.

    Note that it is equivalent to the hint OPTION (FAST 1).  The OPTION (FAST 1) is preferred as the FASTFIRSTROW syntax is deprecated.
    Wednesday, May 20, 2009 12:49 AM
  • To add to what BinaryCoder said...

    Look at the Northwind database...

    SELECT OrderID,CustomerID,OrderDate
    FROM Orders
    ORDER BY OrderDate

    There's an index on OrderDate, so you'd think that SQL would use that index to retrieve the orders to ORDER BY that field.

    But if you look at the query plan for that query, SQL instead actually does a scan on the table and SORTs the results.

    If you add a OPTION (FAST 1) to the query, then it WILL use the OrderDate index in order to get the first record out as soon as possible, but the query as a whole will most likely be slower, because, for each entry in the OrderDate index it finds, it has to do a lookup into the clustered index of the table to retrieve the CustomerID.

    This is why the original query plan used a SORT instead... SQL figured it was worth the cost of doing the SCAN/SORT as opposed to all those lookups for each row.
    --Brad
    Wednesday, May 20, 2009 1:20 AM
  • A couple more references from the SQL Server Optimization Team for the truly interested reader:



    Wednesday, May 20, 2009 5:19 AM