The following analogy works great for both DBAs who need to explain query optimization to non-technical people and for those database developers who are learning how SQL Server “thinks” about working with queries. I use it constantly. I will be referring to this frequently during my series of performance tuning posts.
If you like it, feel free to steal it. Warning: rather than abuse the words “like” and “as” too much, I am explaining the elements of the analogy parenthetically along the way. Now that you have been sufficiently warned, let’s move on to…
A database query is like a super-secret government agent working in the mid-1960s. The agency they are working for has initials that are unfamiliar even to conspiracy theorists . Say that you are this super secret agent. You have been given an empty desk and the instructions that when your boss (a.k.a. a user) asks for information (query results), you need to provide it to him as quickly as possible. The information you need to find is in the super-secret warehouse (the database server) on the other side of the city (the network.)
The boss comes in. He’s a big, mean looking, and schooled in the arts of killing and maiming without remorse (Sounds like an average user to me.) He orders you to give him the total number of suspected blue-haired grandmothers in the U.S. who currently own at least 1 tie dye t-shirts (Ah! A database request!) This is top priority; the security of a peaceful planet depends on it. “Right away” you reply as you’re grabbing your inconspicuous trench coat and car keys. You head out the door with no other thought on your mind but to satisfy this dangerous man.
You jump in your car and head for the highway (a network packet moving through the, you guessed it, network.) After about 25 minutes of bumper-to-bumper traffic (network congestion), you finally navigate your way to the underground fortress that houses all the information. A brief three-minute stop to allow the guards to check your name is on the list (authenticating security credentials), and a quick strip search (made me laugh), you’re on your way to the belly of the repository that is buried underneath a mountain (the beloved SQL Server.)
With vim and vigor you jump out, grab the first box of vanilla file folders that document the nefarious activities of everyone in the world (first few records of a table), and begin digging through the “A’s” looking for any grannies. As you find them, you also check to see if they have hair. If so, you also double-check that it’s blue. The first box down, you grab the next. Two and one-half years later, you have completed looking through all the boxes and gratefully navigate the highways and byways to return to the office with the answer in hand!!
The boss is somewhat surprised to see you! You proudly arrive and state “63!! The total number is 63!!” He sneers a thanks at you, muttering something about how long it took.
“OK,” he says, “Now I need to know how many of them are over the age of 60.” You are crest-fallen. You do not relish the idea of going through all that again.
The smarter super secret agent:
Work smarter, not harder, right? Super-Secret Agent #2 (SSA2) is in the same boat. The same desk, same job description, same distance to the warehouse. When his big, mean looking boss asks for the same information, he hops in his car, crosses city traffic, and passes the security checkpoint. However, rather than going directly to the files, he’s smart enough to go to the “Cross-Reference” room. Once in this room, he pulls out the “Women of the World Index” book and the “Fashions Index” book (Indexes!) and begins to cross-reference the two. About three hours of book research later, he has his list of 63 candidates. Now all that remains is to look up only these few files to see who are not yet 60 and cross their names off the list of candidates.
With answer in hand (a somewhat surprising 52) he starts back for the office. The SSA2 worked much smarter and produced the answer in only about 3 hours where the first agent took 2 and half years.