select top 1 * from table is slow?

Question

select top 1 * from table is slow?

NJ-DBA

SSChampion

Points: 13834
More actions
May 11, 2010 at 4:23 pm

#143056

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?

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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

LutzM SSC Guru Points: 107049 More actions · Answer 2

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]

WayneS SSC Guru Points: 95409 More actions · Answer 3

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

LutzM SSC Guru Points: 107049 More actions · Answer 4

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]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

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

LutzM SSC Guru Points: 107049 More actions · Answer 6

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]

NJ-DBA SSChampion Points: 13834 More actions · Answer 7

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.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

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

NJ-DBA SSChampion Points: 13834 More actions · Answer 9

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

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

Rob.Simpson SSC Journeyman Points: 77 More actions · Answer 11

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

SELECT TOP 0 * FROM table;