Re: Varchars Vs Chars - Performance

>Subject: Varchars Vs Chars - Performance >From: Denham.M@amstr.com >Date: 25 Jun 1997 20:43:15 -0400 >Message-ID: <5osdv3$3h0@cssun.mathcs.emory.edu> > >All > >Here are some results from a recent set of tests that we performed >to compare the merits of using varchars and chars. The table shows >the difference in performance of defining a column as a char instead >of a varchar (positive %ages). > Inserts Selects Update1 Update2 Deletes >table 1 5% 2% 22% 8% -8% >table 2 7% 6% 24% 8% 1% > >Average 6% 4% 23% 8% -3.5% > >All varchar columns are defined as n,0. The largest varchar column >in these tables is 40 chars long. > >These tests would appear to show that char fields are a better bet >than varchar (at least up to a column size of 40 chars). > >Interestingly enough, deletes were faster on the tables containing >varhars. > >This is not the first time that I have seen tests like this support >using char fields over varchars. > >Anybody got views/comments? > >Mark >MarkDenham@aol.com (home) >Denham.M@amstr.com (work) Since varchars are "compressed", that means that the location to any field past the first varchar field has to be caluclated. For rows where all of the fields are fixed length, the individual field is simply an offset into the physical row. Not so for varchars. Each field has to be examined to find the physical location of the next field. 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. Madison Pruet