Most application performance problems involve searching and scrolling. Once you find what you’re looking for, you generally can process it quickly. However, with increased use of automated interfaces, frameworks, and generic coding techniques and tools, this problem seems magnified. While changing programming techniques and customizing code is time-consuming, it’s necessary if you want to save processing time and costs over the life of an application. Besides, you typically apply custom solutions as the exception, not the rule, when building an application.
The techniques presented here can help you solve performance issues when using DB2’s searching and scrolling capabilities.
DB2 Built-In Support for Search and Scroll
DB2 has some built-in support for improving searching and scrolling; some features have been around awhile, but others are relatively new:
- Scrollable cursors (DB2 Version 7)
- Multi-row fetch (V8)
- Backward index scan (V8)
- Soundex function (DB2 9)
- Index on expression (DB2 9)
- Hash access (DB2 10)
- Temporal search (DB2 10)
- Improved in-list matching (DB2 10)
- Improved pagination optimization (DB2 10).
Most performance improvements don’t come from these features, but from the proper coding of predicates or by leveraging the flexibility of SQL to obtain the desired performance.
Scrollable cursors let you move forward and backward through a result set using relative positioning. They can be static scrolling, which places results in a declared temporary table, or dynamic scrolling, which operates against a base table. They can also be sensitive, meaning changes to data during scrolling are visible, or insensitive, meaning changes to data during scrolling aren’t visible to the cursor. Scrollable cursors let you position anywhere in the result set and move backward and forward in a result set.
Multi-row fetch is a fantastic performance enhancement that should be a part of any design that fetches more than a handful of rows per cursor. For random access, multi-row fetch can be a great CPU saver. In some cases it has reduced our CPU utilization by as much as 25 percent for random cursors fetching around 20 rows. For sequential batch cursors, multi-row fetch has saved us as much as 60 percent CPU utilization, with a similar percentage savings in elapsed time processing for large result sets. You can fetch up to 32,768 rows in a multi-row fetch operation. However, IBM recommends about 100 rows for optimum performance vs. thread storage consumption. We usually recommend a maximum of 50 rows, or if you know how many rows your cursor will return, you can specify slightly more than that or 50, whichever is lower. You can get multi-row fetch automatically for read-only remote cursors, but for traditional batch and CICS programs, you must code it yourself.
Backward Index Scan