Re: Varchars Vs Chars - Performance -Reply

In article <5p8gme$cqu@cssun.mathcs.emory.edu>, Peter Tashkoff <TASHKOP@kiwi.co.nz> writes >>>> SaTriGuy <satriguy@aol.com> 27/June/1997 >Lots snipped. >>> Bottom line - a decision has to be made which >>>is more important, the cpu cycles or the disk >>>space. For my money, the cpu cycles are more >>>important. > >This may not always be the case. >We have a replication facility that uses a log table >of 16 key names, and 16 key values to allow >asynchonous discrete data replication. >Although we have allowed up to 16 key fields, the >reality is however that most of our tables have a >single key. >This table was initially configured as char(18) for >the fieldnames and char (40) for the key values >and when making (for us) large replications of say >250,000 records, was blowing the size of this table >right out. In many instances, holding the key value >of the [to be] replicated record was taking far more >space than the record itself. Most of the fields were >empty. We have moved these fields to be varchar >and now do not have many of the problems that we >previously had. >Varchar has proven to be a useful facility for us in >this context, and as a DBA, has made my life a lot >easier. >rgds >Peter Tashkoff <tashkop@zespri.co.nz> >Zespri International Limited >This posting may not be used by any party to vilify >another. Standard disclaimers apply. > Varchars are really useful when a) most rows in the table contain few characters e.g. < 20 b) The user wants to have a large number for the maximum size of the field e.g. up to 200 chars. This means the CPU overhead is swamped by the savings in disk I/O since you are reading a lot less blocks of the disk. Remember 1 disk rotation or 1 disk seek takes the same time as LOTS of CPU cycles. This is why on-the-fly disk compression products like Stacker on PCs can actually make PC's with fast CPUS run faster... -- David Williams