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

stefan wrote in message <344919E4.67E314E3@weideneder.de>... >Kate_Tomchik@HomeDepot.COM wrote: >> >> I have a question about how Informix uses VARCHARs >> internally. >> 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. This is incorrect. If you specify a minimum storage amount for a varchar, that amount of space is reserved in the relative position of the varchar (i.e. if the varchar is in the middle of the row, the space will be in the middle, etc.). There is no padding of anything at the end of the data row. >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. Um, not exactly correct. The minimum storage for a varchar is 1 byte, whcih would be for a null varchar value with no minimum storage specified. The one byte stores the length, even for a null string. If a variable length row (i.e. a row containing one or more varchars) is updated and increases in length such that it will no longer fit on the "home" page, the entire new row (if it is < 1 page in length) will be relocated to a new page. The row in the home page is replaced with a single 4 byte forwarding pointer. Stefan is correct that this is to preserve the rowid which must remain with the row for its entire life in the database. >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. This is misleading and thus incorrect. It is not just the varchar column that gets replaced, but the entire row (which allows the remaining space to be reclaimed in the page, so other rows may avoid being forwarded). Also, when forwarding is done it is applied to the entire row, not just individual columns. There are exceptions when the row itself is longer than can fit on a page (this topic took up about 6 pages in the training material). >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 This is a good tip. You want to avoid forwarding rows, as that then requires 2 virtual i/o operations to retrieve the row data. Do this to a lot of your data and you WILL see performance degradations. Use VARCHARs judiciously. Sadly, Oracle uses a different implementation (all their chars are varchars), so an indiscrete migration can result in some serious performance problems. >> 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? The space allocated for the minimum of one column will NOT be used for an expanded version of another column; that reserved space is preserved in the new "version" of the row. >> Thanks for the Info. Please don't respond if your not SURE I wrote the training materials on this, so I am VERY sure. It is very complex and confusing, with a lot of "if this, then that" situations. The best recommendation I can give is, if you really don't need the disk space savings that a varchar MIGHT give you**, don't use them. **I say "might" because when a newly added record containing a varchar is placed in the table, the engine seraches for a page with enough space to hold the MAXIMUM size the record could be, i.e. the size with all varchar fields full. This means that rows defined with large varchar fields but with those fields not fully populated could well result in a lot of wasted space per page, effectively defeating the space conservation the varchars may have been used to achieve. -- Dave "That's just my opinion - I could be wrong." - Dennis Miller