Re: How does Informix evaluate the = in a join or slect

Albert wisse wrote: > > > > > ---------------------------------------------------------------------------------------- > > Art S. Kagel wrote: > > > BTW if the column is not variable length then use char and avoid varchar. > > What is the difference in speed if the key has data type char or varchar. > About diskspace; I don't care diskspace is cheap what I want is speed. Index usage is the same as any varchar is padded to maximum length for use in an index so the indexing of a varchar and a char of the maximum length is the same. However, fetching the data row can be slower with varchar for two reasons. If the row in inserted with no value or a short string for the varchar column and the varchar is updated later with a longer string the row may have to be moved to another page with a forwarding pointer left in the original rowid slot. This means two I/Os to get the data instead of 1. If the data is inserted with the varchar populated with its final value there may still be a CPU penalty at FETCH time if the calue is FETCHED into a fixed length host variable (in esql char, fixchar, or string -vs- varchar) as the data has to be padded to full length every time that it is fetched. If the field is always full then you are wasting the disk space to store the varchar's current length and the CPU time to strip it out of the output buffer. The only time that varchar is faster than char is if the varchar field has a large maximum and a small reserve/increment (ex varchar(255,1)) and the data is inserted in final form with a large percentage of rows containing very few characters, especially if there are more than one varchar column like this. Then more rows will tend to be stored per data page and fewer I/Os may be needed to fetch data. Again index I/Os are unaffected so indexed queries benefit less from this feature. The final answer is that varchar can be as fast as char or slower than char depending on the actual data being stored and how it is being used at FETCH time but is rarely faster. Art S. Kagel