select top 1 * from table is slow?

  • Some users complained about slow db performance so I ran some queries against the dmvs to look at queries that generate the most IO and queries that have the longest elapsed time. In both results I found quite a few instances where a version of

    select top 1 * from table

    is near the top of the list.. where the only change is the table they are looking at.

    Now, without wondering why they repeatedly need to see the first row in the table (I'll ask that question too), I have a question as to why this would generate so much I/O. When I display the estimated execution plan for these queries, I see that SQL Server wants to do a table scan.

    Should this be relatively quick since we are just looking at the top row?

  • What is the query you use to determine the IO of these queries. Some DMVs are cumulative in nature and thus could be skewing the numbers.

    As for the select top 1 *, the application may be doing that in order to populate a data grid with the column names.

    The table scan may not be an issue. What kind of indexes do you have on those tables that are involved in this kind of activity?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • The big question is: TOP 1 ordered by what column?

    You should figure out what criteria will define the order to select top 1 and add those as an ORDER BY clause.

    The next step would be to check if the columns specified in ORDER BY are part of (preferred) the clustered index or at least covered by a non-clustered index.

    Finally, you should verify if SELECT * really is required or if the number of columns to be returned by this statement can be reduced. If so, you should check if it would benefit overall performance if those columns where added to the index as included columns.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • In addition to the above, what about a where condition. Where's on columns not in an index can force a table scan.

    One common use of the top 1 is to filter (where ) and order. For instance, if I do a select top 1 OrderDate from orders where OrderCustomer = x ORDER BY OrderDate DESC, I'll get the last ordered date for the specified customer. This can actually be faster than a MAX. But I suspect that your problem deals with columns not in indexes (in the where or order clauses).

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • CirquedeSQLeil (5/11/2010)


    ...

    As for the select top 1 *, the application may be doing that in order to populate a data grid with the column names.

    ...

    If that's the only purpose of the query why not using the following?

    SELECT *

    FROM table

    WHERE 1=2



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • lmu92 (5/11/2010)


    CirquedeSQLeil (5/11/2010)


    ...

    As for the select top 1 *, the application may be doing that in order to populate a data grid with the column names.

    ...

    If that's the only purpose of the query why not using the following?

    SELECT *

    FROM table

    WHERE 1=2

    That is another option. I have seen it both ways. Hard to say with just the barebones Select top 1 * that the OP has given.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • CirquedeSQLeil (5/11/2010)


    lmu92 (5/11/2010)


    CirquedeSQLeil (5/11/2010)


    ...

    As for the select top 1 *, the application may be doing that in order to populate a data grid with the column names.

    ...

    If that's the only purpose of the query why not using the following?

    SELECT *

    FROM table

    WHERE 1=2

    That is another option. I have seen it both ways. Hard to say with just the barebones Select top 1 * that the OP has given.

    I just verified the result and it seems like the solution I suggested doesn't touch an index (or the table) at all. It's a simple constant scan.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Thanks for the info guys- I don't know yet why they are doing "select top 1 * from table"... waiting for that info back from the app admin. I like the 1=2 if all they are doing is getting columns. If so I'll suggest that.

    As for indexes- various depending on the tables- there's lots of tables so giving you all the specifics is probably more trouble than it's worth.

    There is no "order by" clause- would totally understand otherwise.

  • Do any of those statements use a where clause in them?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • nope- no where clause. Just "select top 1 * from table"... that's it.

  • NJ-DBA (5/11/2010)


    nope- no where clause. Just "select top 1 * from table"... that's it.

    K - thanks

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • ... or rather than SELECT * FROM table WHERE 1 = 2;

    SELECT TOP 0 * FROM table;

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply