Re: varchar performance

In article <19970706174101.NAA08283@ladder02.news.aol.com>, SaTriGuy <satriguy@aol.com> writes >Well -- > >A while back I put a memo out concerning chars vrs varchars and >performance issues. I guess that I didn't do a very good job making my >point. - must have >been too sleepy at the time. Had a lot of people to question my sainity. > >Anyway, let me further explain what I should have said. > >First of all - I feel pretty strongly that cpu bottlenecks should be >avoided at all costs. There is a tendency to design systems so that more Agreed, however most database systems are disk I/O and possibly network I/O bound not CPU bound. The idea with varchars is to increase CPU usage but decrease disk I/O in the hope of getting a net gain. >cpu cycles are required than are absolutly needed. While this is no big >deal with single datum and small amouts of data, it can become a real drag >when the system is enlarged and we start trying to process millions of >rows. > >Next - varchars have a built-in overhead. In order to process any row >containing varchars, the row must be normalized. That means that we have >to calculate the start and end of each varchar, as well as any datum >beyond the varchar field. While this additional overhead is no big deal >for small populations, it adds up for larger populations. > True, but the disk I/O also goes up and so to the number of disk seeks (even if just track to track) which are REALLY expensive. >Now -- does this mean that varchars should never be used? Absolutly not. > >However, it does mean be careful where they are used. > True. >1) I'd be really hard pressed to see the advantage of varchars when the >max size is realitively small. For instance, if the max size is only 20 >characters, then I'd be rather hard pressed to use a varchar. There might >be some reason that I might chose a varchar, but I doubt it. > Agreed. The average size of rows read from disk must be lower than the maximum by a certain factor. The factor depends upon the relatively cost of CPU usage vs disk I/O and is different for each platform. >2) If I'm going to use varchars, then I'd do everything I could to ensure >that >the varchar field was static. When a varchar field is expanded, we try to >store that row on the same page, but it might no longer fit. Since we >have to maintain the rowid of that row, what we have to do is to store >that row on a different page where it will fit, and then mark the original >page with a "forward pointer" marker. That means that any future access to >that row will actually require two page accesses, one on the original page >and then one on the new page containing the row. If the varchar field is Agreed. Or rebuild the table on a regular basis as performance degrades ;->. >going to change, then I'd consider using a partition text field instead of >a varchar. > ?? What is a partition text field ??? >3) If I'm going to used varchars, then I'd do everything that I could to >avoid a sequential scan of the datapages by any query that I was doing. >This is to avoid the cost of expanding and calculating the location of any >filters in a query against the table. That also means that I might need >to have more indexes on the varchar table than on a table without >varchars. > Agreed, I always try to avoid sequential scans at muh as possible as they are always slow anyway. > >We've had several cases where customers would use varchars where they >really weren't desirable. I know of some where everything that could be a >char field >was always defined as varchar(255,0). They never had a char field. >Needless to say, they had performance problems. Glad you cuold help them, this is an interesting topic and one I have not seen come up before (since I have never used varchars). Ooo! Something new! Question:- I have a 4GL application DATABASE djw MAIN DEFINE x LIKE tab1.col SEELECT col1 INTO x FROM tab1 END MAIN This is compiled to access tab1.col as a char(255). What happens if I change the column to varhar(255) and then try to run the application again WITHOUT RECOMPILING? [Some of the binaries on some of out sites are dated 1993, if we recompile them they may fail due to library changes / confusion about the correct version of source code - Hey I only joined here in 1996.] >Madison Pruet -- David Williams