When asked what’s the single most important or stressful aspect of their job, DBAs typically respond with “assuring optimal performance.” Indeed, a recent Forrester Research survey indicates that performance and troubleshooting top the list of most challenging DBA tasks. With this in mind, let’s take a moment to outline the basic factors that influence the performance of DB2 applications.
A proper investigation of DB2 performance issues should begin with database design, but let’s start with a discussion of SQL, as it impacts more users.
As you write SQL statements to access DB2 data, there are certain very simple, yet important, rules to follow to encourage efficient SQL. Of course, SQL performance is a complex topic and to understand every nuance of how SQL performs could take a lifetime to master. That being said, adhering to the following simple rules puts you on the right track to achieving high-performing DB2 applications.
1. The first rule is to always provide only the columns you need to retrieve in the SELECT-list of each SQL SELECT statement (i.e., do not use SELECT *). SELECT * is fine for quick and dirty queries but is bad practice for inclusion in application programs because:
- DB2 tables may require additional columns in the future. SELECT * will retrieve those new columns, too, and your program may fail unless it’s modified.
- DB2 will consume additional resources for every column that must be returned. If the program doesn’t need the data, it shouldn’t ask for it. Even if the program needs every column, explicitly code each column by name to avoid the previous pitfall.
2. Do not ask for what you already know. This may sound simplistic, but most programmers violate this rule at one time or another. For a typical example, consider what’s wrong with the following SQL statement:
SELECT EMPNO, LASTNAME, SALARY
WHERE EMPNO = ‘000010’;
Give up? The problem is that EMPNO is included in the SELECT-list. We know that EMPNO will be equal to “000010” because that’s what the WHERE clause specifies. But with EMPNO in the WHERE clause, DB2 will dutifully retrieve that column, too. This causes additional overhead, which will degrade performance. The overhead may be minimal, but if the same SQL statement is run hundreds or perhaps thousands of times a day, then that minimal performance impact can become significant.
3. Use the WHERE clause to filter data in the SQL instead of filtering it in your program. This, too, is a common rookie mistake. It’s much better for DB2 to filter the data before returning it to your program because DB2 uses additional I/O and CPU resources to obtain each row of data. The fewer rows passed to your program, the more efficient your SQL will be. So, the following SQL is better than simply reading all the data without the WHERE clause and then programmatically checking each row to see if SALARY is greater than 50000.00:
SELECT EMPNO, LASTNAME, SALARY
WHERE SALARY > 50000.00;
4. Avoid writing SQL to access DB2 tables such as flat files. Programmers often fall prey to repeating tactics that previously worked. In general, this can be a useful strategy because it reduces your coding effort. But do not misuse DB2 by accessing it like non-database data. DB2 isn’t designed to mimic the old master file processing tactics of QSAM files (i.e., reading a record from a file and then using a value from that record to drive reads from an existing file). Novice DB2 coders try to mimic this processing using two cursors: one to read a row and the other using a value to drive the next cursor. This is a recipe for poor performance. Instead, code the SQL as a join and let DB2 do the work for you.
5. Finally, put as much work as possible into the SQL and let DB2 optimize for you. With appropriate statistics and proper SQL coding, DB2 almost always creates more efficient access paths than you can code into your programs.
These rules aren’t the “be-all, end-all” of SQL performance tuning—not by a long shot. Although in-depth tuning may still be required, these rules will ensure you aren’t making “rookie” mistakes.