ASP 101 - Active Server Pages 101 - Web06
The Place ASP Developers Go!

Please visit our partners


Windows Technology Windows Technology
15 Seconds
4GuysFromRolla.com
ASP 101
ASP Wire
VB Forums
VB Wire
WinDrivers.com
internet.commerce internet.commerce
Partners & Affiliates














ASP 101 is an
internet.com site
ASP 101 is an internet.com site
IT
Developer
Internet News
Small Business
Personal Technology

Search internet.com
Advertise
Corporate Info
Newsletters
Tech Jobs
E-mail Offers

ASP 101 News Flash ASP 101 News Flash



 Top ASP 101 Stories Top ASP 101 Stories
Migrating to ASP.NET
Getting Scripts to Run on a Schedule
The Top 10 ASP Links @ Microsoft.com

QUICK TIP:
Variable Name Prefixes
Show All Tips >>
ASP 101 RSS Feed ASP 101 Updates


Quick Tips


Don't Use Select *

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 NameData TypeIndexed?
IDIntegerPrimary Key
SomeDateSmallDateTimeIndexed
AnotherValueVarchar(500)Not Indexed

If you look at the execution plan for the following very similar queries you find very different results.

  1. SELECT ID, SomeDate FROM MyTable
  2. 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!  ;)


Previous      Show All Tips      Next

If you have a tip you would like to submit, please send it to: webmaster@asp101.com.


Home |  News |  Samples |  Articles |  Lessons |  Resources |  Forum |  Links |  Search |  Feedback

Internet.com
The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers