Tom Muck

Alpha Dog Blues Band
Home page
All articles
All Extensions | Extension News | Extension FAQs | Customer Login
Books authored or co-authored by Tom Muck
Extensions, books, and other products | Customer Login
Your current cart contents
Tom-Muck.com Blog | CMXTraneous Blog | Flash Remoting Blog
About the site

Blog

Tom Muck's BlogTom Muck's Blog

News and Views

Bad practice in SQL

Sunday, November 07, 2004 9:45:59 AM

I have noticed over the years a very bad practice in using the SQL language: SELECT * FROM mytable. This is one of the worst things you can do for performance of your application. The performance hit is roughly equivalent to the percentage of fields that you don't need on your page. For example, if you only need 2 fields for your web page, but you are doing SELECT * and retrieving 10 fields, the statement will be about 80% slower than if you only selected the 2 fields in the statement:

SELECT * from mytable 100ms

SELECT fname, lname FROM mytable 20ms

Say you have 10 fields in your table. You might think that you are being more efficient by writing this:

SELECT * FROM MyTable

instead of this:

SELECT fname, lname, address, city, state, zip, phone, email FROM mytable.

Since I am using 8 fields in the statement, I am not retrieving data for 2 fields. The timed results might look like this:

1st statement: 100ms

2nd statement 80ms

This is true for the majority of databases and scripting languages across the board (PHP, ColdFusion, ASP, etc). I've even seen pages where a user will write SELECT * FROM mytable and use only one field, where the database table has 20-30 fields in it. This is outrageous, in that the performance penalty is 95-97%. In a busy web application, this can be disastrous.

As a side note, when people send me pages to look at, they are very hard to debug when they use SELECT *. For one thing, I do not have access to your database, so I have to recreate the SQL statement to work with one of my sample databases. It's much easier to do when I can see what fields I need to use.

Category tags: Dreamweaver, ColdFusion, SQL

Before posting comments or trackbacks, please read the posting policy.

Full Blog Calendar

Pay me securely with your Visa, MasterCard, Discover, or American Express card through PayPal!
Pay me securely with your Visa, MasterCard, Discover, or American Express card through PayPal!
About | Privacy Policy | Contact | License Agreement | ©2002-2024 Tom Muck | Dreamweaver Extensions