Re: VARCHAR2 vs. CHAR

Jacob Love <jlove_at_engin.umich.edu> wrote in article <5i0i96$1bn_at_srvr1.engin.umich.edu>...
> Varchar2 stores a variable length string up to 2k and there is no
> storage penalty for strings shorter than the maximum specified. CHAR
> datatype will pad any string that does not fill the specified length.
> Padding makes the SQL a bit trickier, so queries and reports all have
> to take it into consideration. While I have occasionally heard that
> CHAR is more efficient than VARCHAR, I don't think even if it is true
> it will have a major impact on most applications. I have used
> varchar successfully since it became a real option with version 7,
> and I wouldn't switch unless the application forced me to do so.

I've heard the same thing about VARCHAR2 being a bit more expensive that CHAR. I didn't think it was true, or that the extra overhead would be negligible. However, the tests we ran showed that you can take a 20-30% performance hit, sometimes more, by using VARCHAR2 instead of CHAR.

The performance hit is most noticeable in COBOL-based apps, which tend to like to deal with fixed-length strings rather than variable length strings.  I'm guessing there is a lot of overhead with the necessary padding and trimming of strings while placing them into the database.

Brian

-- 
Brian M. Biggs              http://www.cincom.com/
Lead Software Engineer      voice: (513)677-7661
Cincom Systems, Inc.

* To reply to me via e-mail, just remove the leading underscore from
  my return address.  I had to do this to cut down on the number of
  spammers pulling my address from UseNet postings and sending me
  junk e-mail.