When SQL Isn't the Best Tool for the Job

Power i Performance Hint

SQL is a powerful and flexible tool – often the best choice when accessing large volumes of data. But it’s not always the smartest option. In fact, for certain access patterns, performance can degrade significantly compared to more traditional methods.

The background

We ran performance tests on different access strategies for reading single records – the typical scenario where a customer calls support and we need to look up one record at a time, without knowing what comes next.

The results? SQL consistently came out as the least efficient option – using up to five times more CPU than a simple CHAIN operation in RPG.

Why does this happen?

SQL is designed to handle sets of data, not individual record lookups. Every time an SQL query is executed, the engine evaluates the best access strategy – a flexible approach, but one that adds overhead. For single-record access, this becomes a performance penalty instead of a benefit.

What are the alternatives?

We tested 16 different solutions. Several of them, including user indexes and binary tables, performed up to 20 times faster than SQL. Here are some highlights:

• RPG CHAIN or COBOL READ: Simple, effective, and still hard to beat for single-record access.
• User Index: Only the necessary fields are stored in the index, drastically reducing CPU and memory usage.
• User Space (Binary Table): Even faster than User Index, though less flexible during runtime.
• Hash Table in Memory: The fastest method in our tests – ideal when the keys are known and predictable.

Why this matters

You don’t need to redesign your entire application to improve performance. In many cases, replacing just one SQL statement with a more efficient method can save hours of runtime – especially in batch jobs processing millions of transactions.

And don’t forget: GiAPA identifies exactly these inefficiencies – by automatically analyzing program activity and flagging potential optimizations. No need for expensive external consultants.