Home > Uncategorized > Don’t Use SELECT *

Don’t Use SELECT *

One of the most common performance and scalability problems are queries that return too many columns or too many rows. I’ve seen many developers actually using and abusing SELECT * FROM queries. SELECT * query not only returns unnecessary data, but it also can force clustered index scans for query plans because columns in the SELECT clause are also considered by the optimizer when it identifies indexes for execution plans.

The following two queries show this difference; note the difference in query plans as well as the relative cost to the batch. The query cost relative to the batch is 67% for the SELECT * compared to just 33% for the SELECT column query.

SELECT    SalesOrderID
FROM    Sales.SalesOrderHeader
WHERE    OrderDate< 01/01/1978

FROM    Sales.SalesOrderHeader
WHERE    OrderDate< 01/01/1978

The first query uses a Clustered Index Scan to resolve the query because it has to retrieve all the data from the clustered index, even though there is an index on the OrderDate column.

The second query uses the OrderDate index to perform an Index Seek operation. Because the query returns only the SalesOrderID column, and because the column is the clustering key, the query is resolved using only that index.

  1. Joel
    December 1, 2009 at 12:17 am

    That said, how often do you only wnat a list of ID\’s from a table, its usually a few key values & perhaps a join to lookup realtion values like customer group name.I guess what you are getting at is be smart about the data you request, yes it may be a performance hit to get an additional field & that needs to be weighted up agains what the user actually "needs" to see.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: