SEO

Hello,

I have a question about VARCHAR fields. Our application groups here
are starting to use VARCHARs much more frequently. Even VARCHAR (2) to
(9) length fields. They say this is because some of the application
programs, specifically Java Beans cannot handle the spaces after the
value in CHAR fields.

Is anyone else seeing this trend?

I know that VARCHAR fields have 2 extra bytes of overhead. Does anyone
know if there is a significant performance impact in DML against these
fields due to tracking the length?

Thanks in advance for any and all information,

Jeff

jdokos wrote:
> Hello,
>
> I have a question about VARCHAR fields. Our application groups here
> are starting to use VARCHARs much more frequently. Even VARCHAR (2) to
> (9) length fields. They say this is because some of the application
> programs, specifically Java Beans cannot handle the spaces after the
> value in CHAR fields.
>
> Is anyone else seeing this trend?
>
> I know that VARCHAR fields have 2 extra bytes of overhead. Does anyone
> know if there is a significant performance impact in DML against these
> fields due to tracking the length?

There is the additional storage overhead as you mention, plus this can
lead to performance issues with row overflows and/or page reorgs.

IMO, this is usually a symptom of lazy programmers rather than "the
app can't handle extra spaces".

Ian wrote:
> jdokos wrote:
>
>> Hello,
>>
>> I have a question about VARCHAR fields. Our application groups here
>> are starting to use VARCHARs much more frequently. Even VARCHAR (2) to
>> (9) length fields. They say this is because some of the application
>> programs, specifically Java Beans cannot handle the spaces after the
>> value in CHAR fields.
>>
>> Is anyone else seeing this trend?
>>
>> I know that VARCHAR fields have 2 extra bytes of overhead. Does anyone
>> know if there is a significant performance impact in DML against these
>> fields due to tracking the length?
>
>
> There is the additional storage overhead as you mention, plus this can
> lead to performance issues with row overflows and/or page reorgs.
>
> IMO, this is usually a symptom of lazy programmers rather than "the
> app can't handle extra spaces".

Is it true that in Informix VARCHAR takes more space than CHAR? In
Oracle the waste of space and CPU comes with working with CHAR and
it has been almost completely abandoned.

Thanks.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

"DA Morgan" <damorgan@psoug.org> wrote

> Is it true that in Informix VARCHAR takes more space than CHAR?

Not at all. Just one byte extra at the beginning to record the length.

> In Oracle the waste of space and CPU comes with working with CHAR and
> it has been almost completely abandoned.

same is true with informix, except that upto a length of char(15) (some say even 20)
the performance gain in char is worth the space wasted. so I would always recommend
a char field upto char(15).
The difference between char and varchar becomes stark in delete-and-load tables.
That is tables which are periodically cleaned and loaded again. A table with fixed-length
columns (char, int, date etc) loads much faster than with variable length table. I think
it is something to do with the engine calculating before inserting on which page the
row will fit.

Ian wrote:

> There is the additional storage overhead as you mention, plus this can
> lead to performance issues with row overflows and/or page reorgs.

This is certainly true, but you also have to keep in mind that more short
VARCHARs might fit on a page than (padded) CHAR values. So you could
easily have a performance benefit because less pages need to be loaded to
satisfy a query.

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena

DA Morgan wrote:
> Ian wrote:
>
>> jdokos wrote:
>>
>>> Hello,
>>><SNIP>
>
> Is it true that in Informix VARCHAR takes more space than CHAR? In
> Oracle the waste of space and CPU comes with working with CHAR and
> it has been almost completely abandoned.

Yes and no. A VARCHAR stored a 1 byte length and an LVARCHAR a 2 byte
length as part of the field, however, both types are true variable length
fields. So, if the field is full it will take a byte or two extra storage
than the equivalent CHAR, if not full it will take less storage than CHAR.

The rub and performance hit for using VARCHAR or LVARCHAR in IDS is similar
to that in DB2. If expanding a variable column in a row causes the row to
no longer fit on the data page which is its home then the row is moved to
another page and a forwarding pointer is left behind in its original
location. This is so that index keys do not have to be rewritten. Beyond
that if expanding a variable lenght field on a row causes the row to become
larger than a page (not possible in DB2, but that's another issue) IDS will
create a remainder page to hold the tails of oversized rows from several
pages and a forwarding pointer to the location of the tail is left at the
end of the home row portion. Since LVARCHARs can be as long as 32K this can
happen several times with a row taking up several full pages and a tail entry.

Usually, performance wise, I find breaking long strings into multiple fixed
length CHAR rows in a child table or by using a MULTISET is far more
efficient and no harder to code for.

Art S. Kagel

rkusenet wrote:
> "DA Morgan" <damorgan@psoug.org> wrote
>
>> Is it true that in Informix VARCHAR takes more space than CHAR?
>
>
> Not at all. Just one byte extra at the beginning to record the length.
>
>> In Oracle the waste of space and CPU comes with working with CHAR and
>> it has been almost completely abandoned.
>
>
> same is true with informix, except that upto a length of char(15) (some
> say even 20)
> the performance gain in char is worth the space wasted. so I would
> always recommend
> a char field upto char(15).
> The difference between char and varchar becomes stark in delete-and-load
> tables.
> That is tables which are periodically cleaned and loaded again. A table
> with fixed-length
> columns (char, int, date etc) loads much faster than with variable
> length table. I think
> it is something to do with the engine calculating before inserting on
> which page the
> row will fit.

Thanks. I will update my information on Informix accordingly.

Daniel Morgan
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

Art S. Kagel wrote:
> DA Morgan wrote:
>
>> Ian wrote:
>>
>>> jdokos wrote:
>>>
>>>> Hello,
>>>> <SNIP>
>>
>>
>> Is it true that in Informix VARCHAR takes more space than CHAR? In
>> Oracle the waste of space and CPU comes with working with CHAR and
>> it has been almost completely abandoned.
>
>
> Yes and no. A VARCHAR stored a 1 byte length and an LVARCHAR a 2 byte
> length as part of the field, however, both types are true variable
> length fields. So, if the field is full it will take a byte or two
> extra storage than the equivalent CHAR, if not full it will take less
> storage than CHAR.
>
> The rub and performance hit for using VARCHAR or LVARCHAR in IDS is
> similar to that in DB2. If expanding a variable column in a row causes
> the row to no longer fit on the data page which is its home then the row
> is moved to another page and a forwarding pointer is left behind in its
> original location. This is so that index keys do not have to be
> rewritten. Beyond that if expanding a variable lenght field on a row
> causes the row to become larger than a page (not possible in DB2, but
> that's another issue) IDS will create a remainder page to hold the tails
> of oversized rows from several pages and a forwarding pointer to the
> location of the tail is left at the end of the home row portion. Since
> LVARCHARs can be as long as 32K this can happen several times with a row
> taking up several full pages and a tail entry.
>
> Usually, performance wise, I find breaking long strings into multiple
> fixed length CHAR rows in a child table or by using a MULTISET is far
> more efficient and no harder to code for.
>
> Art S. Kagel

Thanks.

Daniel Morgan
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

As has been said before. The issue of any varchar vrs a char boils down to
how hard it is to get to the variable portion of the row. With fixed length
objects, sequential scan filtering is much easier (i.e. require fewer cpu
cycles) than having to calculate where the variable portion is. There are
several strageties used to calculate variable sized objects, but all
solutions do require some form of calculation and calculations do require
cpu cycles.

Some DBMS use null terminated strings, but then that means you have to
always scan to find the variable lengthed object. Some will split the row
into fixed sized objects and then create an array of offsets for the
variable length objects. The variable length objects may be stored in a
variable portion of the row, or outside of the row in a LOB type of object.,
or possibably in a secondary row. In that case, the varchar still requires
additional space because you have to have some form of a pointer to the
physical location of the varchar. The cost may be hidden, but it is still
there.

Even using a null terminated string for a var char requires an additional
space for the NULL. So that solution is going to use the same space as the
IDS varchar, except instead of a column size, there is the column null
character. And that solution is probably the worst to navigate because each
character in the column must be examined just to get to the end of the
column.

"rkusenet" <rkusenet@hotmail.com> wrote in message
news:3j2s8sFn8t3jU1@individual.net...
> "DA Morgan" <damorgan@psoug.org> wrote
>
> > Is it true that in Informix VARCHAR takes more space than CHAR?
>
> Not at all. Just one byte extra at the beginning to record the length.
>
> > In Oracle the waste of space and CPU comes with working with CHAR and
> > it has been almost completely abandoned.
>
> same is true with informix, except that upto a length of char(15) (some
say even 20)
> the performance gain in char is worth the space wasted. so I would always
recommend
> a char field upto char(15).
> The difference between char and varchar becomes stark in delete-and-load
tables.
> That is tables which are periodically cleaned and loaded again. A table
with fixed-length
> columns (char, int, date etc) loads much faster than with variable length
table. I think
> it is something to do with the engine calculating before inserting on
which page the
> row will fit.

Please see my comments about varchar vrs char. Don't forget, a null
terminator requires a character position as well. ;-)



"DA Morgan" <damorgan@psoug.org> wrote in message
news:1120679155.210606@yasure...
> Ian wrote:
> > jdokos wrote:
> >
> >> Hello,
> >>
> >> I have a question about VARCHAR fields. Our application groups here
> >> are starting to use VARCHARs much more frequently. Even VARCHAR (2) to
> >> (9) length fields. They say this is because some of the application
> >> programs, specifically Java Beans cannot handle the spaces after the
> >> value in CHAR fields.
> >>
> >> Is anyone else seeing this trend?
> >>
> >> I know that VARCHAR fields have 2 extra bytes of overhead. Does anyone
> >> know if there is a significant performance impact in DML against these
> >> fields due to tracking the length?
> >
> >
> > There is the additional storage overhead as you mention, plus this can
> > lead to performance issues with row overflows and/or page reorgs.
> >
> > IMO, this is usually a symptom of lazy programmers rather than "the
> > app can't handle extra spaces".
>
> Is it true that in Informix VARCHAR takes more space than CHAR? In
> Oracle the waste of space and CPU comes with working with CHAR and
> it has been almost completely abandoned.
>
> Thanks.
> --
> Daniel A. Morgan
> http://www.psoug.org
> damorgan@x.washington.edu
> (replace x with u to respond)