While crafting a title for this month’s blog entry, I discovered that the phrase, the long and short of it, which this title is based on started out a little differently. The original phrase used to mean the essence of something was actually - the short and long of it.
This original ordering of the phrase
mirrors the evolution of table names on Db2 for i. Initially, table names were
limited to a short 10-character maximum length. That max table name length
jumped to 128 characters with the V3R1 release. Based on the 128-character max
length for table names, you may think that the only consideration in choosing a
new table name is how descriptive you want your name to be. The descriptiveness
of the table name is definitely a consideration, but there’s also a performance
consideration you’re probably unaware of.
As mentioned earlier, names were
initially limited to a length of 10 characters. This limit was not just for Db2
tables names, it was the limit for all object types (data queues, journals,
etc.) supported by the operating system.
As a result, the internal system function that is used to resolve
unqualified object names like the reference to table1 in the following SQL was
built to support 10-character names.
SELECT column1 FROM table1
When Db2 for i was enhanced to
support table names up to 128 characters in length, the plumbing used by that
internal system function was only extended to support object names up to 30
characters in length. Does that mean you can’t have unqualified references to
table names greater than characters long? NO – it just means that it will take
longer for the system to resolve unqualified references to tables with a name
longer than 30 characters.
The next logical question is: how much longer does it take? Simple
performance tests on my system (your mileage may vary) showed the response time
of an SQL request being almost 1.5% slower with a 40-character table name
compared with the same SQL statement using a 20-character table name. This same performance overhead would also apply
to unqualified SQL view and alias names greater than 30 characters long.
This overhead may not seem like a
big performance difference, but if you multiply that across hundreds or
thousands of SQL statement executions on your system it could be significant. As
I’ve highlighted on this blog before, little things can add up when you’re
talking about SQL performance.
George Washington, the first U.S.
president, agreed with this thinking. According to the Daily Stoic, one of Washington’s
favorite sayings was “many mickles makes a muckle”. That saying was from
a Scottish proverb that highlighted that things add up – even little ones.
And that’s the long and short of it
when it comes to performance considerations for SQL table, view, and alias
names.
