Tom 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
Posted by Tom Muck
Add comment | View comments (1) | Permalink | Trackbacks (0) | Digg This
Before posting comments or trackbacks, please read the posting policy.