Re: Details about the internal use of VARCHAR in 7.X servers

Hi, Kate_Tomchik@HomeDepot.COM wrote: > > I have a question about how Informix uses VARCHARs > internally. We are using a few packages > from vendors who have converted their code from Oracle. In > Oracle it is advantageous to use VARCHARs, so they have them > everywhere including the Primary Key fields. We are having > severe performance problems with this system, and wanted to > understand better (basically so we can explain it to the vendor) > why this is detrimental. In my notes from an INFORMIX > training class, I have written DO NOT USE VARCHARs in > INDEXES. Can someone elaborate on that? Why didn't you ask your instructor ? Far as I know, VARCHARSs are treated like CHAR columns inside an index. The indexed values are stored with the max. fixed length. If you want to be sure, simply dump the index pages. Last time I did it on 7.10UD1. > > Also, quantitatively how much better is the performance of > update/insert queiries if the VARCHARs are at the end of the > line instead of the middle? Is that only an advantage if the > varchar declares a minimum value and gets padded, or are > there other situations where this would help performance? Padding characters will be stored at the end of the data row, if you defined a minimum value in your VARCHAR. I do not really know if this kind of storage is an advantage. > > When we program the use of VARCHAR fields we usually initialize > them to 1 blank byte with no minimum value, and later fill the field > in with comments. Is there a way we could change this to > optimize performance? 1st. Each VARCHAR will cost at least 4 bytes in your data row. These bytes are neccessary for chaining. If you have only one column ( in this case a VARCHAR column ) in your table and if you change a short string to a large one that will not fit into the current page any longer, then the new value will be stored in another page. Because the "rowid" of the row must not change ( it's stored inside your index ) Informix has to chain the new row with the old one. The old value of the VARCHAR will be overwritten with the rowid of the new row ( Forward Pointer ). This forward pointer has a 4 byte length. 2nd. If the contents of a column will not be changed ( i.e. your firstname ), then there is no need to define a minimum for the VARCHAR. Otherwise the minimum value is usefull. Sometimes it's a good idea to set the minimum value in your VARCHAR definition to the average number of characters you will store in the column . > > If there are more than one VARCHAR fields in a record, and > they both have minimum values, can either one use up the > padding reserve for the other when updated, or does the > whole record rewrite with the padding needed for the > usused variable? See description above. > > We're looking at changing our modeling procedures based > on this information, so I want to be sure I know all the facts. > > Thanks for the Info. Please don't respond if your not SURE > > you know the answers. I appreciate it! =|:- ) Bye Stefan