Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2010
    Posts
    75

    Unanswered: does adding TOP to select speed anything up?

    Simple question really, Ive got a query that selects from an enormous table, joins to several look up tables, and returns a large result set. The query is very slow, even after optimizing it several times, adding the appropriate indexes, etc.

    I want to know if adding TOP to the select statement will speed anything up? Ive done some testing and the results are very inconsistant, so its hard to say if there is any benifit.

    Right now the query can easily return 100,000 rows, but we've seen that the user only cares about the first 100 rows in most cases. So I'm thinking of changing it to "Select TOP 100 ..." and only fetching more than that if its necessary.

    I realize that limiting it to 100 rows will obvioulsy speed up the transfter time of sending the results from SQL server to the client, but will it actually speed up the query itself? Or will it not speed anything up because it has to perform the entire query anyway, and then only take the top 100 once the query is completely finished anyway? Whats the theory here?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If the result set needs to be sorted before applying the TOP, then the TOP will actually slow the query by an inifinitesimal amount. If the result set can be returned without a sort (a so called "native fetch") before applying the TOP, then the optimizer can take advantage of it. Depending on which build of SQL Server you're running and what query you're running on it, TOP could affect the time needed to run the query.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan View Post
    If the result set needs to be sorted before applying the TOP...

    If the result set can be returned without a sort...
    the way i understood the original post, there's no ORDER BY clause involved

    TOP without ORDER BY makes no logical sense whatsoever

    TOP with ORDER BY will only reduce the number of result rows transferred, so, yeah, it could improve performance, but only by a small amount

    it'll be the ORDER BY that's either slow or not slow
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If the ORDER BY clause can use an index (which is ultimately determined by the query optimizer), then the query can use a natural fetch even with an ORDER BY clause. That can make a TOP run a whole lot faster, IF the optimizer and the SQL engine support it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2010
    Posts
    75
    The query does do an ORDER BY. The ORDER BY column is the clustered index column.

    So Im confused. Will adding TOP increase performance? - Other than obviously reducing the number of rows that need to be transferred.

  6. #6
    Join Date
    Feb 2010
    Posts
    75

    Parameters slow down stored procedure

    edit: oops meant to start a new thread - new topic

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Two "ifs" to consider:

    If the query optimizer picks a plan that returns rows in "natural fetch" order.

    -and-

    If the SQL engine running your query is "smart enough" to take advantage of the natural fetch.

    --then--

    the TOP <n> will in speed up your query.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •