Don't Use Select *

(Digest from www.asp101.com)

---

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.