VARCHAR: Friend or Foe

Posted by

I read an article from Craig Mullins on “Working with variable data“. The rest of this post is going to assume you’ve read it, so click through and then come back.

I’ve seen performance issues caused by VARCHAR before, and Craig makes some interesting points. VARCHARs are actually one of the big reasons to Reorg frequently. His post is geared toward the mainframe, but much of it applies on DB2 for LUW too.

To expand on what he’s talking about, here’s a query that will help you find columns in your database that are VARCHARS with a length of less than 30. You obviously may want to look at columns on a variety of criteria, not just  ones with a short length, but this makes for an easy example for this post.

select  substr(tabschema,1,18) as tabschema,
        substr(tabname,1,30) as tabname,
        substr(colname,1,30) as colname,
    from syscat.columns
    where       typename='VARCHAR'
                and length < 30
                and tabschema not in ('SYSIBM', 'SYSIBMADM', 'SYSCAT')
    with ur;

The output from this query looks something like this:

TABSCHEMA          TABNAME                        COLNAME                        LENGTH
------------------ ------------------------------ ------------------------------ -----------
XXXXXXXX           ADDRESS                        BILLINGCODE                             17
XXXXXXXX           BUADDR                         BACODE                                  17
XXXXXXXX           BUADDR                         BASTATE                                 20
XXXXXXXX           BUADDR                         BAZIPC                                  20
XXXXXXXX           BUSPROF                        EMPLOYEEID                              20
XXXXXXXX           BUSPROF                        ALTERNATEID                             20
XXXXXXXX           BUYORG                         BUSTATE                                 20
XXXXXXXX           BUYORG                         BUZIPC                                  20
XXXXXXXX           COUNTRY                        CALLINGCODE                             16
XXXXXXXX           EMLBOUNCED                     STATUSCODE                              10

Now in a WebSphere Commerce database, there’s not a lot I can do about most of these – though I’ll do a similar analysis for custom tables, which by the Commerce standards should all start with ‘X’, and are thus easy to filter out with a where clause in this query.

Assuming you have tables you may be able to change, you’ll want to go into further detail on them like Craig recommends – something like this – using the query from Craig’s post:

            10 as "AVG_SPACE_AS_CHAR(10)",
    WITH UR;
---------------------- ------------------------- ---------------------------- --------------------- ------------------------ --------------------------
              20222270                   8088908                     12133362                    10                        4                          6

  1 record(s) selected.

Note, that you want to change the ’10’s in the above query to whatever length of column the VARCHAR you’re working with is. In this case, if I were to define this column as CHAR(10) instead of VARCHAR(10), I would lose about about 11.5 MB. Small potatoes. In this case, it’s not a likely column to change much once the record is inserted (for a US-based business, Countries don’t often change, and knowing this client, I suspect there are only two possible two-character values). If, on the other hand, it were a column that was likely to change, there could be significant overhead associated with moving the record, so it might be worth going for the CHAR. You don’t want to impact query perfromance for just a miniscule 11.5 MB of space.

Note that the change from CHAR(10) to VARCHAR(10) causes no essential change in functionality – the same lengths of data can be stored.

In my specific example, maybe the better question would be can we do a shorter length – is there really a case where we would need all 10 characters, when most of my rows are only 2 characters? Let’s look at the data in the same format as above with that change:

The query would be:

            2 as "AVG_SPACE_AS_CHAR(10)",
    WITH UR;

Note that I’ve changed some of those 10’s to 2’s to reflect the length change I’m considering.

--------------------- ------------------------- ---------------------------- --------------------- ------------------------ --------------------------
              4044454                   8088908                     -4044454                     2                        4                         -2

  1 record(s) selected.

I can see that I could save just under 4 MB by changing this column to a CHAR(2). I’m quite sure that it’s not worth it here, but there are hundreds of little decisions like this that add up when you’re building custom tables, so it’s sure something to keep in mind.

I do review tables that my developers write, and one of the things I look for is VARCHAR(1), VARCHAR(2), and VARCHAR(3) – since these almost never make sense.

Lead Db2 Database Engineer and Service Delivery Manager , XTIVIA
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.

One comment

  1. Thanks for the great post.

    Changing the datatype from varchar to char for a given column may result in

    a) data mismatch if the same column is being used in local or join predicate.
    b) varchar returns value without padding where as char returns back with padding

    Good developer would handle these two things with trim function. Esp it would be difficult to review the entire code if it is already in production.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.