locked
Select Top 1 performance question RRS feed

  • Question

  • Hi All -

    I  have a question about querying a view, basically comparing a SELECT TOP 1 * vs just a SELECT of columns.

    When I run a SELECT * FROM the view, the query returns thousands of rows and takes several minutes. When I run in ssms, the data begins to display to the query window almost immediately. But it does not complete until maybe 30mins or so later.

    When I run it with a SELECT TOP 1 * (and with no order by clause), nothing is displayed to the ssms query window right away and it takes the 30+ mins before the one row is displayed.

    My question is if this is expected?  Since I am not applying an order or any filter criteria, does sql need to basically query everything and then return the top 1?

    Thank You!

    Wednesday, May 1, 2013 9:55 PM

Answers

  • When SQL Server execute query, it creates execution plan, which should be optimal for specific case.

    In most cases plan for TOP 1 will be different than plan for returning all rows. It is possible that plan which is supposed to quickly return one row is bad.

    Please post execution plans for both statements, without these we will not be able to help too much.

    Thursday, May 2, 2013 3:19 AM

All replies

  • What is your SQL Server version and can you post the view definition? 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, May 2, 2013 2:36 AM
  • When SQL Server execute query, it creates execution plan, which should be optimal for specific case.

    In most cases plan for TOP 1 will be different than plan for returning all rows. It is possible that plan which is supposed to quickly return one row is bad.

    Please post execution plans for both statements, without these we will not be able to help too much.

    Thursday, May 2, 2013 3:19 AM
  • Hello,

    Thank you for the replies.

    We are using sql server 2008 R2.

    I will try and get the execution plans and view/query definition and post it later today. Unfortunately, I just got some other work from my manager that is a higher priority for me.

    Thursday, May 2, 2013 2:58 PM