DB2 SQL Tips: 10 Performance Guidelines

Recently I discussed DB2 SQL tips that have provided application performance for decades with my clients’ SQL developers.  These DB2 SQL tips provide developers with performance guidelines that can help their application designs and SQL coding get the best performance the first time.

Since clients have many different DB2 SQL tips, I’ve listed below 10 performance guidelines that have helped my clients and will hopefully be beneficial to your developers.  Please pass along any additional DB2 SQL tips or ideas and I will make sure they are posted in the comments of this blog.

1. Only SELECT the columns needed because it optimizes the I/O between DB2 and your program.
2. Only SELECT rows needed for the process or transaction and use FETCH FIRST x ROWS ONLY whenever possible.
3. Code WHERE clauses with columns that have unique or good indexes defined.
4. Reference only tables that are absolutely necessary for getting the data.
5. Code as many WHERE column clauses as possible because the DB2 data engine filters data faster than any application code.
6. Prioritize the WHERE column clauses to maximize their effectiveness. First code the WHERE column clauses that reference indexed keys, than the WHERE column clauses that limit the most data, than WHERE clauses on all columns that can filter the data further.
7. Code SQL JOINs instead of singular SQL access whenever possible.  A single SQL JOIN is always faster than two SQL statements within an application program comparing and filtering the result set data.
8. Code SQL tables JOINs when there is a common indexed column or columns shared by both or all of the JOINed tables.  If a common column is not indexed talk to a DBA and make a new index if possible.
9. When coding JOINs make sure to code the most restrictive JOIN table first and provide as many indexed and restrictive WHERE clauses as possible to limit the amount of data that needs to be JOINed to the second or subsequent tables.
10. Use DB2 OLAP DB2 functions to optimize your DB2 SQL answer result sets.  Using these SQL DB2 OLAP functions is usually much faster than application code summing, totaling and calculations over your data.

There are always more DB2 SQL tips and I have mentioned many other DB2 performance aspects in other blogs. Here’s a list with links to other posts that may help you improve your DB2 performance, security, and ease-of-use:

5 More DB2 SQL Performance Tips
Another 5 More DB2 SQL Performance Tips
5 Big Data SQL Performance Tips – Fixing Generated SQL
DB2 11 SQL Performance Improvements
5 More SQL Performance Tips for your Big Data
Hadoop SQL: 4 Reasons Why BigInsights Is the Best
Vital Java DB2 SQL Performance Considerations
More DB2 Family Security Best Practices Part 7: Preventing SQL Injection
DB2 SQL Security Audits

Thanks for all supportive emails. I appreciate everyone reading my blog.

——————————————————————-

Have you made your plans for IDUG in Orlando this year?  Also the make sure to register early and get the IDUG early bird discount. Sign up today!

I look forward to speaking at the IDUG DB2 Tech Conference 2013 North America conference.  The conference will be held in Orlando, Florida on April 29-2, 2013.  Get more information at www.idug.org.

I will be speaking at the conference presenting Session F07 – “Data Warehouse Designs for Big Data Performance” on Wed, May 01, 2013 (02:15 PM – 03:15 PM) in Bonaire 5 & 6.

——————————————————————-

Dave Beulke is an internationally recognized DB2 consultant, DB2 trainer and education instructor.  Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments.