---
title: "DB2 SQL Query Performance Tuning (3 of 3)"
description: "SQL performance tuning tips for database developers. Read more."
url: https://srinimf.com/2015/05/20/start-tuning-your-db2-sql-query-part-1/
date: 2015-05-20
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2015/05/pexels-photo-1193743.jpeg?fit=1880%2C1253&ssl=1
categories: ["SQL"]
tags: ["SQL tips and tricks"]
type: post
lang: en
---

# DB2 SQL Query Performance Tuning (3 of 3)

Tune your SQL query for better performance and save CPU utilization.

Related[13 Phases DB2 Load Utility](https://srinimf.com/2014/12/18/db2-load-utility-executes-in-13-phases/)

Related[Subquery Vs. Correlated Subquery – Top Differences](https://srinimf.com/2012/07/16/sql-sub-query-and-correlated-query/)

## 6 Ideas [to Tune SQL](https://srinimf.com/2015/02/14/5-more-db2-sql-performance-tips/) Query

1. **Check the predicates. **Whether it is one query or multiple queries in a program, check every predicate in every query to ensure that they are indexable, stage 1, and as simple and straightforward as possible.
2. **If there is a Distinct or Group By** in the query, make sure it is needed and then look at the Explain to see if it is causing a sort to take place. If the Distinct or Group By is needed, maybe there is another way to rewrite the query to handle the issue of a duplicate that will not cause a sort.
3. **Execute an Explain**. In the Explain output, check the following: Are any tablespace scans occurring? Are any sorts occurring? If the query has a Union, Distinct, Group By, or Order By in it, does it need to be there?
4. **When JOINS involved**, what is the order of tables being processed? DB2 should be selecting the table that will be filtered the most as the starting table. If it is not selecting the table being filtered the most, then check the columns of the predicates and make sure there are enough statistics on these columns to help the optimizer. To determine which table is going to be filtered the most, you must know the values coming in at runtime. You can execute Select count(*) statements to figure this out.
5. **All correlated subqueries** should use an index, and if possible, they should process with the index only = yes. A correlated subquery is a subquery that contains a join to a column from the outer table.
6. **Any nested loop join** operations should have their tables processed using an index with matching columns. If the starting (composite) table is showing a tablespace scan, then this may not be much of an issue due to the fact that it will be scanned only one time. But for a joined table, any tablespace scans will cause that table to be scanned numerous times.

Related Posts

- [DB2 SQL Query Performance Tuning (1 of 3)](https://srinimf.com/2019/03/17/3-sql-tuning-ideas-on-table-columns-to-apply/)DateMarch 17, 2019
- [3 Must-know Tips Before You Write SQL Query](https://srinimf.com/2018/05/13/sql-code-review-tips/)DateMay 13, 2018
- [DB2 SQL Join: Here are Best Practices](https://srinimf.com/2014/09/15/db2-joins-performance-tuning/)DateSeptember 15, 2014

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2015%2F05%2F20%2Fstart-tuning-your-db2-sql-query-part-1%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2015%2F05%2Fpexels-photo-1193743.jpeg%3Ffit%3D1200%252C800%26ssl%3D1&description=DB2%20SQL%20Query%20Performance%20Tuning%20%283%20of%203%29)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2015/05/20/start-tuning-your-db2-sql-query-part-1/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2015/05/20/start-tuning-your-db2-sql-query-part-1/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2015/05/20/start-tuning-your-db2-sql-query-part-1/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2015/05/20/start-tuning-your-db2-sql-query-part-1/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20DB2%20SQL%20Query%20Performance%20Tuning%20%283%20of%203%29&body=https%3A%2F%2Fsrinimf.com%2F2015%2F05%2F20%2Fstart-tuning-your-db2-sql-query-part-1%2F&share=email)
-

### Like this:

Like Loading…