Archives of the TeradataForum

Message Posted: Thu, 07 Apr 2016 @ 13:45:17 GMT

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

I always thought that the primary issue when designing a table was the question of whether the performance of loading was more important than reading from the table?

In this case, you have a growing and performance sensitive workload inserting into the table. How big is this table. What's happening on the query side? Where does your performance priority lie? Are these simple inserts (INSERT/VALUES)? Are they PI inserts?

In the past, I've had the situation where inserts from multiple sources were being written to the same table. These were simple PI inserts, where row hash collisions were the performance issue. The table was relatively small and reading from it was infrequent. In looking further, it was likely that queries of the table would always result in full-file scans. So the performance of any query was not that important and the focus should be on the inserts.

To improve insert performance, we decided that we could minimize row hash collisions by increasing the width of the primary index. We added additional existing columns (such as SHORT_DESC and CAT_ID) to the PI. This change would prohibit any query from doing a PI retrieval, but it didn't matter since we had already been living with full-file scans.

We had also considered using some kind of sequence (or system assigned) number, but adding the existing columns was sufficient. Another advantage of using the existing columns was that it didn't require any changes to the application.

Re-designing the PI resulted in improved insert performance. It's been a long time and I don't remember how much faster, other than it was significant.

I found a post that I wrote in 1999 - it might also be of some help:

teradataforum.com/teradata/19990722_174621.htm

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