Why SELECT * Queries are bad
20 July 2007 in Databases
Index coverage seems to be the one of the reasons. Selecting specific columns can make a query one hundred times more efficient.
A really nice post from Jon Galloway explains more about SELECT * queries performance. You can find it here.
Search
Pages
Top Posts
- 15 Visual Studio .NET Add-Ins you won't live without
- Using SQLite in .NET
- How to get started with Silverlight Streaming
- Avoid chaos, don't let bugs take your project away
- Best-Ever Ad from Microsoft
Categories
- All
- Random tidbits
- Links
- Windows
- Fun
- Portuguese
- WebDev
- Programming
- Blogging
- Personal Experience
- Microsoft
- Tech
- OS
- Linux
- Security
- TV
- Software
- Databases
- Hardware
- WPF
2 comments. Add your own comment.
Vitor says 21 July 2007 @ 07:03
The article title is wrong, and so are the conclusions. The described performance degradation does not happen only when we use “select *”. It happens every time we select a column that is not indexed or included in an index. Even if you were selecting just one column, if that column was not indexed, the access plan would be roughly the same as for “select *”. As a matter of fact, if all the table columns were included in the index for the column ReferenceOrderID, the “select *” statement would have the same access plan as when selecting just one column.
PS: I think “select *” queries are bad, not just for the reason described in the article
One of the reasons why it shouldn’t be used is because of data model evolution. With “select *” the application has no control over which columns are being returned, what can easily break an application if the DBA decides to add an extra column to the table.
fabiopedrosa says 21 July 2007 @ 13:40
That’s true. I didn’t want to break the title chain
Indeed, the index coverage will only prevent a scan if all the columns are covered.
Even so, the reason you mentioned is also in the original post, as the Stability Aspect. Very short, but there.
Leave a Comment