As our IBM Technology Expert Labs team performs SQL performance assessments for clients, we
get the opportunity to review a wide variety of SQL statements. These SQL statements run the gamut from simple
to head-scratching to inefficient.
One
inefficient SQL coding technique that I’ve recently seen way too much is the same
cursor name being used over and over again. I was a developer once, so I
understand the common coding practice of copying existing code (i.e., cursor
declaration) and customizing the copied code to meet your needs. However, the
customization of copied cursor code should include changing the cursor name and
I’ll explain why.
Inputting a
cursor name like C1 or CUR1 is fast and easy, but there are a
couple of reasons why you should avoid reusing the same cursor name in your
application programs. The reasons really come down to avoiding chaos which is
defined as complete disorder and confusion.
The cursor name chaos impacts both the Db2 for i engine and your IBM i team.
First,
duplicate cursor names can add a small amount of overhead to the runtime
execution of your cursors. Db2 creates runtime data structures at program
execution time to support the usage of your cursors. Once these runtime data
structures are created for a cursor, Db2 tries to reuse the runtime data
structures on future usages of that cursor within a job or database
connection. Db2 uses the cursor name to
find these cached runtime data structures.
If your program has a half dozen cursors all named C1, then Db2 must do
more work to figure out which runtime data structures belongs to the 6 different
cursors named C1. If each of the cursors in the program had a unique name, Db2 is
able to more quickly find the associated runtime data structures to reuse.
Second,
duplicate cursor names add overhead and complexity to usage of SQL analysis
tools such as those found in the IBM i Access Client Solutions (ACS) SQL
Performance Center. Below, you’ll find a screen capture from my recent usage of
the Plan Cache Snapshot tooling on a client’s snapshot.
I think you’ll agree that the repeated cursor names make it difficult to differentiate between the long list of C1 cursors. At first glance, you can be tricked into thinking that they’re all the same cursor. The user must scan their eyes further down the cursor declaration to find the C1 cursor that they’re trying to analyze and tune. Making the Statement column wider helps, but I think it’s easier when the leading characters of a cursor definition clearly define the cursor.
At this
point, hopefully you’re convinced that cursor names matter. If so, the next
logical question to ask is: how should I name my cursors? I believe the best
approach is to use a name that describes the cursor result set from a business
perspective. Cursor names like LatePayments or LowInventory would be good
examples of this approach. If you think the business perspective approach
requires too much thinking, then consider using the program name as a suffix or
prefix. For example, the two cursors
declared in PgmA might be C1_PgmA and C2_PgmA.
Either of
these cursor naming approaches is going to eliminate the overhead of finding
the cached runtime data structures. In addition, they provide context when you’re
reviewing a list of queries using ACS tools to analyze the Plan Cache or a Plan
Cache Snapshot. More descriptive cursor names definitely make it easier to
understand the who and why related to the usage of a cursor.
Now that you
understand there’s more than meets the eye when it comes to cursor names, I
hope that you’ll be updating the SQL coding standards at your shop to avoid the
chaos.
