February 20, 2018
Craig Mullins just came out with this post on writing SQL to maximize performance
This got me thinking about a point in my career when most of the work I performed was SQL tuning. While there's an axiom about the best performing SQL statement being one that doesn't execute, if you're actually going to write functional SQL, you should try your best to understand the DBMS SQL engine. Unfortunately, many programmers/developers don't take the time to do this, so the SQL they write is generic and may not perform as well as it could.
Again, writing efficient SQL takes effort and understanding. So study the Db2 optimizer rules and focus on these tips:
- Whenever possible, use stage 1 rather than stage 2. Stage 1 predicates are better because they disqualify rows earlier, reducing the amount of processing that's needed at stage 2.
- Write queries to prioritize the evaluation of the most restrictive predicates. When predicates with a high filter factor are processed first, unnecessary rows are screened early on; this can reduce processing costs down the line. However, a predicate's restrictiveness is only effective among predicates of the same type and at the same evaluation stage.
I encourage you to read Craig's work, and catch him at the upcoming IDUG conference.
Here are additional resources that can help you write efficient SQL running on Db2 for z/OS:
- "DB2 12 for z Optimizer."
This IBM Redbook is authored by by Terry Purcell, the lead designer responsible for the Db2 for z/OS optimizer.
- Db2 12 for z/OS Managing Performance.
This IBM Knowledge Center document explains how to use predicates efficiently.