Bookmark and Share

Recent Posts

Learning to Code Efficient Db2 SQL

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:

Posted February 20, 2018| Permalink

comments powered by Disqus