You’ve been coding feverishly for ten minutes. You’ve got twenty table joins lined up, and you’re ready to execute your newest monster SQL query. A single bead of sweat rolls down your forehead as you timidly press the button to execute. You watch the SQL engine spin as it starts to process your query. One second ticks by. You think, “No syntax errors!” Three more seconds. Now you’re starting to get nervous. At the ten-second mark, nervousness gives way to panic. Now you think, “Where’s my data?” At twenty-two seconds, two records finally come back from your query. “That’s the data I’m looking for, but why did it take so long?” you ask yourself.
Even the best query writers have been here.
So, how do you write better SQL queries to improve your execution?
- Be Linear
Map out a linear path from table to table to table – to get from your starting point table to your destination table.
- Be Methodical
The method through which you join two tables or filter results matters. When it comes to joining tables, choose indexed key columns as much as possible. And, pay attention to column order in those indexes. Order matters. Use the same order in your joining clauses and filtering clauses.
- Be Iterative
Build and execute your query one new joined table at a time. If your query performance falls off drastically with the addition of a new table join, refine your join or filter before you move on and add another table join.
- Be Creative
Is that next join the best join? Can you get to the same destination through an alternate set of table joins? Don’t be afraid to experiment with everything. Try a new join method. Try a new join path through different tables. Try a different order of filter columns. There’s more than one way to skin a vulptex.
- Be Scientific
Don’t accept anything at face value. Hypothesize. Experiment and observe. Draw conclusions.
Make a change in your query. Evaluate if the result was good or bad, and keep it or toss it accordingly. Pretty soon, you’ll start to notice the pattern, and then you’ll be thinking more like a SQL engine.