IDS Forum

Re: Performance impact of LVARCHAR

Posted By: Art Kagel
Date: Tuesday, 15 September 2009, at 10:29 p.m.

In Response To: Performance impact of LVARCHAR (MARK COLLINS)

What version of IDS are you running?

COBOL is handling the column as a CHAR column and the engine is doing the
type conversion.

A 5000 character column will be causing the row to taking up at least 3 data
pages on disk. And since it is being inserted using a CHARACTER type host
variable, it is taking up the full 5000 bytes plus the two byte length word
on disk. You really are not getting any advantage from using an LVARCHAR
over using a simple CHAR(5000) column. The additional pages of storage
tends to be very inefficient. However, it may simply be the size of the
column rather than anything inherent in the LVARCHAR data type. The only
real overheads of using LVARCHAR is the 2 byte length word prepended to the
column's data string and the conversion function calls to convert it to a
simple CHAR array padding any missing trailing spaces.

When you say it is slow versus another table without LVARCHAR, does that
table have very wide rows, similar to this table in the > 5000 byte range?
The comparison may not be fair.

Anyway, the main advantage of using VARCHAR and LVARCHAR columns instead of
CHAR is that the table only stores the actual characters that are in the
host variable when the row is added thereby saving some storage for columns
variable length but stable contents once inserted. IB that like an ESQL/C
FIXCHAR column, the COBOL CHARACTER field is padded on the right with spaces
to its full length. That means that you may be storing all 5000 bytes of
this column even when the application only placed a few significant
characters in the field.

Art

Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.

On Tue, Sep 15, 2009 at 7:10 PM, MARK COLLINS <markc@myfastmail.com> wrote:

> We use COBOL to access Informix. The ESQL/COBOL product has not seen an
> update
> in over a decade. The manual has a print date of April 1996, version 7.24.
> Obviously, we're talking old. That predates the Informix buyout of
> Illustra,
> so there is no concept of user-defined datatypes.
>
> Which brings me to my question. Does anyone know how big of a performance
> impact there is when inserting into a table that contains a column
> LVARCHAR(5000)?
>
> The manual says it is "implemented as a built-in opaque data type"
> (
> http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqlr.doc/ids_sqr_125.htm?resultof=%22%6c%76%61%72%63%68%61%72%22%20
> ),
> but I have no idea how the COBOL pre-compiler can handle this since the
> data
> type did not exist that far back.
>
> The program runs, and the results seem correct, but it is extremely slow
> compared to similar activity against a table with no LVARCHAR columns.
>
> Anyone heard rumors of IBM updating the ESQL/COBOL product?
>
> Thanks.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--001517448ad2df32470473a8ab22

Messages In This Thread

• Performance impact of LVARCHAR
  MARK COLLINS -- Tuesday, 15 September 2009, at 7:10 p.m.
  • Re: Performance impact of LVARCHAR
    Art Kagel -- Tuesday, 15 September 2009, at 10:29 p.m.
    • Re: Performance impact of LVARCHAR
      MARK COLLINS -- Wednesday, 16 September 2009, at 10:44 a.m.
      • Re: Performance impact of LVARCHAR
        Art Kagel -- Wednesday, 16 September 2009, at 12:13 p.m.
        • Re: Performance impact of LVARCHAR
          MARK COLLINS -- Wednesday, 16 September 2009, at 12:22 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.