Details about the internal use of VARCHAR in 7.X servers
Posted in 1997
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? 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? 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? 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? 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! =|:- )