Top 13 SQL Server Mistakes and Missteps – #12 SELECT *
#12 SELECT *
Plain and simple this is a lazy habit that causes performance problems and possible code fail over the application lifecycle.
That is all.
“Explain myself” you say? Fine.
Imagine you’re given a grocery list by your Significant Other that states: “Go to the Sunny Mart and buy everything. It’s exactly what we need.” Perhaps it even is exactly what you need – everything from the local convenience store. You need two cases of beef jerky, Jiffy Pop (eight of them), twelve 40’s of three different kinds of malt liquor (sorry to introduce math into this post) among all the other things that the Sunny Mart carries (beer, wine, porn, lightbulbs, Back-Off mud flaps, windshield wiper refills, gum, bleach, chips, ice…) You’re busy, and you have a lot of hobbies. Roll with the analogy will you!
The store later works out a deal with the local distributor for turkey bacon. Being a solid citizen, you find turkey bacon, Steak-ums, Meet-Strips and any other non-bacon-baconesque product an affront to civil society and humanity as a whole.
What the cluck?
Yet, your grocery list still states “EVERYTHING”. You don’t want to upset your Significant Other ever so you spend the cash, take the time, and dump the turkey bacon in the garbage after you bring it home. You’ve made the effort to bring home the bacon, um er, turkey bacon and had no use for it. Why would you waste the resources to do so? What kind of hell would break loose if the store started carrying diet pills, fart pads, <REDACTED> enlargers, merkins, RU-486, or horse tranquilizers. Besides being a totally kick-ass corner market, your Significant Other may be offended if you show up back at home with one or any combination of those items so you would need to do the dumpster dump before walking in the door. “Honey, what the *#@! is the deal with the <REDACTED> enlarger and horse traquilizers? And what is this red toupé-thing with the hole in it?
SELECT * is that grocery list.
When you issue a SELECT * statement to return all columns from a table you’re bringing all those bits back to your client, dumping anything you don’t need, and moving on. You invalidate any use of any covering indexes created to improve query performance, you incur additional overhead on the network when returning results, and depending on where you weed out the unnecessary columns, you incur processing overhead on the client or the application server – all before returning results to the end user. This manifests as slowness to the user at the keyboard. They get upset and call the DBA saying there is something wrong with the database. The DBA’s coffee gets cold while researching what turns out to be bad development practices. The developer, after a result of a tragic accident involving coffee mug used as a hammer and a pencil through the back of his hand has to learn how to type with his left foot. All because of laziness.
For the want of a nail, the keyboard smelled of feet the war was lost.
Conversely, what happens when Sunny Mart starts changing its inventory? It finds that with the exception of some crazy bastard that comes in once every few months and buys EVERYTHING, they can’t give away a gross of Playboy air fresheners or the assorted line of Bob Barker’s DIY Home Spay & Neuter Kits it stocks. They stop carrying them. Your grocery list will no match with the inventory at the convenience store. Therefore your request can’t be fulfilled properly and your Significant Other IS NOT PLEASED.
This is where the management for the Information Technology department should come down hard on both DBA and Development stacks. The DBA should not be altering database schemas without understanding the nature of how that database is being used in the application space. Any schema changes should be communicated to those resources in the Development stack as well. The developer should continue to be subjected to office supplies crucifiction because of their use of lazy coding.
If you still don’t get why SELECT * is a waste of resources and a performance impact I’ll offer the following analogies; hopefully one speaks to you in a language you are able to understand:
For the Homeopathic Herb Entheusiast: SELECT * is like dropping coin on a 1/4 ounce of George Doubleblew Kush when a bowl full of shake would have done the trick.
For the Red Wings Fan: SELECT * is like winning the President’s Trophy for the regular season and then losing 0-4 in the first round against the Avs.
For Steve Jobs: SELECT * is like developing a kick ass phone and hosting it exclusively on a crappy cell network.
For Bill Gates: SELECT * is like paying an application development staff for two years worth of work and being delivered Microsoft Bob.
For the Beer Snob: SELECT * is like wanting a Bud Lite and paying for a beer.
For the Bachelor Party Organizer: SELECT * is like paying for twins and directing one of them to be in charge of working the bar (not an euphamism.)
For the Movie-Lover: SELECT * is like, well, did you ever see The Crying Game? It’s like that, kinda.
For the Music Fan: SELECT * is like Van Halen – with Gary Cherone.
For the College Student: SELECT * is like that last drink of the night. You were probably good two drinks ago.
For the Starbucks Customer: SELECT * is like paying Venti, needing tall.
For the Amputee: SELECT * is like buying a pair of anything. Or a set of Throx
For Lance Armstrong: SELECT * is like saying he has balls.
For Patrons At a Chinese Restaurant: SELECT * is like ordering Kung Paõ Chicken and getting Kung Paõ Chow and Kitten “Chicken”
One Just For the SQL DBAs: SELECT * is like every non-pass-through query coming from Microsoft Access.
For the Star Wars Geek: SELECT * is like buying the entire Collectors’ Edition Director’s Cut Box Set on Blu-Ray then tossing Episodes I-III into the CD shredder at work. Nevermind, that is a propper handling of Episodes I-III.
Anyone else out there have a way of explaining what SELECT * is like?
Next up #11: SQL Memory Settings
What is your opinion of the relatively standard OOP practice of classes that are wrappers for tables? This usually means the OOP developer that wants one piece of information about a record from a table instantiates an object whose constructor loads everything, ala select *, from that table? The rationale is that it is significantly faster for the developer to instantiate a class and use it than to write queries (or tell the DBA to write queries). Developer/DBA time is usually more costly than compute time.
The class could be written to load every piece of information on demand, but that’s hardly efficient for a use that actually does use most/all of the columns in the table.
The class could be written with an optional “load all” kind of parameter, where the developer could choose whether or not the particular use of an object would require most. This seems like it’d be bad both ways, as only using 3 of 10 fields might not be enough, meaning three trips to the database, or using 4 of 10 is enough, meaning 6 or 10 fields returned aren’t used.
Instantiation could specify a list of fields to load and error out on any use that wasn’t in the list, but that’s defeating a primary purpose of OOP: let developers focus on business logic and not plumbing.
So while I can agree with the theory, I’m not convinced it stacks up against practical use in the real world as an absolute.