Something you see in a lot of database access code is a select
statement that looks something like this:
SELECT * FROM TableName WHERE ...
While there's technically nothing wrong with it,
using the SELECT * syntax could be stealing away precious performance
from your application, and even it it's not now, it might someday soon.
When you do a SELECT * against a database table it tells the database to
return all the fields from the selected rows in the given table.
The problem with doing that is that you rarely actually need all the fields
for any one page and moving around all that extra data can really slow things
down. This is especially true if your database and web server run on separate
computers since the extra data then needs to be transferred over the network.
The response that I usually get when I tell people this is that the table is small and
it doesn't really matter. I wholeheartedly disagree. Even if you're retrieving data
from a table that only contains a few fields, how do you know that table will only
contain those fields in the future? Very few databases and web development projects
are static. Things change and your application needs to be able to roll with the punches.
Here's an example to illustrate my point.
Let's say that you've got an employee table in your database that lists your
current employees. You'll obviously want a page on your intranet (and maybe even your
public site) that lists these employees. Assume the employee table contains just a few fields:
id, first name, last name, department, and phone number. If you were to build a
simple phone list that lists employees by department and provides their phone numbers you might
use a database query something like this:
SELECT * FROM Employee
as opposed to typing out what you really mean:
SELECT id, first_name, last_name, department, phone_number FROM Employee
Right now there's really no difference between the two, but if six months down the road
you decide to add a picture of each employee to the database, are you going to remember
to go back and change the SQL query? If not, now with every call to that page the web
server requests every field and is transferred a picture of each employee that it
doesn't even use. Since pictures tend to be large, you'd be transferring a lot of
data for no reason!
It may take a few extra seconds to type out the field names, but it's a good habit to
get into and the performance savings can make it well worth the time.
Update: Yet Another Reason Why You Shouldn't Use SELECT *
While it's still falls under the heading of performance,
one of our kind readers took the time to write in and share yet another reason not to
use SELECT * when you build your SQL queries. Here's his e-mail:
Read the tip on your site entitled "Don't Use Select *" and it neglects to mention an
extremely important reason that the advice in the title of the article is so important. More
important than pulling back more data than needed from a table is the fact that if your query
really only needs to return fields that are part of a key, then it can return the results of
the query from the index scan without doing the extra step of looking up the data in the table
itself to pull in the extra fields.
Consider the following table:
MyTable
Field Name
Data Type
Indexed?
ID
Integer
Primary Key
SomeDate
SmallDateTime
Indexed
AnotherValue
Varchar(500)
Not Indexed
If you look at the execution plan for the following very similar queries you find very different results.
SELECT ID, SomeDate FROM MyTable
SELECT ID, SomeDate, AnotherValue FROM MyTable (The net result of SELECT * FROM MyTable)
When I run these two queries through Query Analyzer to get the execution plan I get two very
different answers from these very similar queries, and also in the batch of these two queries it
shows a huge performance disparity. Query 1 is 13.38% to Query 2 with 86.62% of the expected
work in a batch consisting of just these two queries.
In essence, what this tells me is that the cost of adding the "AnotherValue" or any
non-indexed field to the query is far greater than just pulling down some extra data and is
definitely an issue even in very narrow tables.
Anyway, my 2 cents. Would have sent it directly to the author but I didn't see him/her
credited on the site for the tip.
John Fuex
Thanks to John for the additional reason not to use the SELECT * syntax. The only reason I didn't
credit the author of the original tip is because
it was my fault
and I didn't want to get blamed for writing it! ;)
If you have a tip you would like to submit, please send it to:
webmaster@asp101.com.
