Re: CHAR() v. VARCHAR()

In article n6c@cssun.mathcs.emory.edu, mstrothr@informix.com (Michael Strother) writes: >} Our DBA claims that VARCHAR() is much less efficient than CHAR(). Yet, >} he claims that both are fixed length columns with null terminated string >} data. He claims that Informix pads the CHAR() columns either at the time >} of the SELECT or at the time of transmission to the client. If both are >} null terminated strings in storage and the CHAR() is padded when >} retrieved, why would CHAR be more efficient for storage of string data. >} It would seem that padding the data at the time of retrieval would make >} CHAR() less efficient re. server resources than VARCHAR(). > > He is wrong, CHAR data types are stored in their full length in the > database table, a VARCHAR is implemented as a fixed length pointer > in the record, my experience has been that there is no appreciable > difference in performance although it could reasonably be argued that > a VARCHAR is slower due to extra disk activity... I generally agree with Mike but prefer to look at the issue in greater granularity. It's very important to sample the data to be stored in the VARCHAR column so that the VARCHAR is sized properly and keeps data fragmentation to an absolute minimum. When deciding whether to use VARCHAR vs CHAR datatypes, I look at the customer requirement for response time, the number of queries which will be hitting the table, whether the table is going to be subjected to a great deal of update activity, and my best estimate on whether the VARCHAR column (with MIN/MAX sized as best as possible for optimal storage benefits) is LIKELY to fragment once updated. An additional consideration is how often I will get the opportunity to reorganize the table involved. After balancing these factors, I decide whether to use VARCHAR or not. Usually I decide in *favor* of using VARCHARs. Not always, but usually. Bill Williams E-Mail: bwilliam@jaxnet.com Flames: nobody@nowhere.com -or- redirect to /dev/null +-----------------------------------------------------------------+ | Disclaimer: My views, opinions, and judgments are my own. | +-----------------------------------------------------------------+ | "Free scientific inquiry" is a redundancy. | | "Free governmental inquiry" is self-contradictory. | | | | Be sure to read "Atlas Shrugged" by Ayn Rand. It's the world's | | greatest novel, rated #2 of those books readers said had the | | most impact on their lives (NY Times readership poll)! | +-----------------------------------------------------------------+