Post by Polansoft

Today, we would like to share a simple real-world example of SQL query optimization in one of the products for Db2. The original query looked like this: SELECT emp_id, salary FROM employees WHERE RTRIM(name) = 'John'; At first glance, it seems harmless. The developer added RTRIM() because "name" value contained trailing spaces. However, applying a function to an indexed column forces Db2 to evaluate the function for every row before comparing values. In many cases, this prevents efficient index usage and can turn an index lookup into a much more expensive task. In order to address the performance issue, a Junior Developer suggested this instead: SELECT emp_id, salary FROM employees WHERE name LIKE 'John%'; While this avoids the function call, it introduces a different problem. LIKE 'John%' doesn't mean "John with trailing spaces." It also matches values such as John Smith or Johnson. Besides returning incorrect results, it may still read more data than necessary. The best solution turned out to be the simplest: SELECT emp_id, salary FROM employees WHERE name = 'John'; Db2 uses blank-padded comparison semantics for fixed-length character data (CHAR). That means trailing spaces are ignored during equality comparisons, so 'John' is considered equal to 'John '. As a result: ✅ Correct results ✅ No unnecessary string functions ✅ Index seek is used instead of a scan ✅ Simpler and cleaner SQL Takeaway: Before adding functions like RTRIM(), LTRIM(), or UPPER() to columns in your WHERE clause, take a moment to understand how Db2 compares the underlying data type. Sometimes the database already handles the edge case for you, and removing unnecessary functions can significantly improve query performance. #Db2 #SQL #QueryOptimization #Database #ProductDevelopment #SoftwareEngineering #EnterpriseSoftware #Mainframe #MainframeModernization #LegacySystems #zOS