Archives of the TeradataForum

Message Posted: Mon, 07 Feb 2011 @ 15:32:10 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Performance impact of VARCHAR size

From:		Dieter Noeth

Attila_Finta wrote:

I have a question about rows per block for a table with VARCHAR columns. I have seen the Row Size Calculation Form in the TD Database Design manual (1094). It seems that the defined length of a VARCHAR column doesn't really matter at all.

Example: a VARCHAR column currently contains values varying from 5 to 15 characters in length; if I define it as VARCHAR(200) vs. VARCHAR(20), there will be no performance difference either way, because the only thing that matters is average size, not max. Therefore there is no penalty to "over-sizing" VARCHAR columns. Correct?

There'e no penalty regarding perm space usage.

But these potential problems come to mind:

- there's a maximum rowsize of approx. 64kb, so there's less available for all other columns.

- the optimizer doesn't know about the average size, so it might decide that a table will not be cached in memory

- FastLoad (MLoad probably, too) calculates based on the max size how many rows will be put into a block sent to Teradata, thus resulting in much more but smaller blocks messages.

- For ORDER/GROUP BY any VARCHAR columns are converted to CHAR, so spool space needed for sorting is much larger now.

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference