Click here to Skip to main content
15,922,630 members
Articles / Programming Languages / SQL
Alternative
Tip/Trick

The Evil That is "Select *"

Rate me:
Please Sign up or sign in to vote.
4.33/5 (3 votes)
1 Aug 2011CPOL 13.1K   12
It also limits SQL's ability to find a proper index to use, hence hindering performance. I wasn't aware of this aspect until a SQL consultant made mention of it. Unfortunately we haven't adopted the policy of dropping the *.

It also limits SQL's ability to find a proper index to use, hence hindering performance. I wasn't aware of this aspect until a SQL consultant made mention of it. Unfortunately we haven't adopted the policy of dropping the *.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Chief Technology Officer
United States United States
What I get paid or have been paid to do:
Sports data (NBA, NCAA)
Restaurant data - menus, locations, sales history, foot traffic, dwell time.
IOT - logistics and mining
Trading software and protocols (QuickFIX, Onix, etc).
Price feed development.
Trader simulation tools.
Back office, end of day reporting, NFA compliance.
Performance testing, optimization, and analysis.
Large scale data management, analysis, and visualization.
Worked with most major banks, some electronic exchanges, many data providors, and a few counter-parties:
MISO, PJM, NYISO, NEISO, CAISO, AESO, ERCOT, BOE, CME, CBOE, Eurex, ICE, Morgan Stanley, J.P. Morgan, RBS, EBS, Credit Suisse, Nomura, Barclays, Deutsche Bank, Citibank, UBS, Goldman Sachs, Bear Stearns (defunct), Triaina, Bloomberg
Experience in FX, energies, futures.

Comments and Discussions

 
GeneralRe: Ok, thanks for the info. Pin
Wendelius25-Jul-11 11:27
mentorWendelius25-Jul-11 11:27 
GeneralSory , I cant find msmits alternative , so I am not changing... Pin
Armando de la Torre11-Aug-11 13:20
Armando de la Torre11-Aug-11 13:20 
GeneralReason for my vote of 1 The columns in the where clause are ... Pin
Armando de la Torre11-Aug-11 11:47
Armando de la Torre11-Aug-11 11:47 
GeneralRe: Armando, would you rethink your vote? Check what msmits wro... Pin
wizardzz11-Aug-11 13:03
wizardzz11-Aug-11 13:03 
GeneralHey Wizardzz, Your consultant was right. See my other commen... Pin
msmits1-Aug-11 20:06
msmits1-Aug-11 20:06 
GeneralRe: Thank you, that is actually how he described it! Thank you! Pin
wizardzz2-Aug-11 4:10
wizardzz2-Aug-11 4:10 
GeneralI agree with Klaus. In the spirit of not throwing my own ki... Pin
Mikel Taylor1-Aug-11 9:25
Mikel Taylor1-Aug-11 9:25 
GeneralRe: That could well be the case, but I'm not quite sure. I'll f... Pin
wizardzz1-Aug-11 9:28
wizardzz1-Aug-11 9:28 
GeneralThis is nonsense - an index is only used based on the WHERE-... Pin
Klaus-Werner Konrad26-Jul-11 6:52
Klaus-Werner Konrad26-Jul-11 6:52 
GeneralRe: That is not true. The optimizer takes into account the cost ... Pin
msmits1-Aug-11 20:02
msmits1-Aug-11 20:02 
That is not true.
The optimizer takes into account the cost of using the index and then finding the record (if the index is not covering). This estimation greatly depends on the distribution of the data (statistics).
Just because you have an index with all fields in the WHERE clause does not mean it will be used. In fact, using an index and then retrieving the other columns may degrade performance.
GeneralThis is interesting. Did the consultant have any proof for t... Pin
Wendelius25-Jul-11 10:39
mentorWendelius25-Jul-11 10:39 
GeneralRe: He did not offer any actual proof. He is a http://www.micro... Pin
wizardzz25-Jul-11 11:10
wizardzz25-Jul-11 11:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.