Archives of the TeradataForum

Message Posted: Fri, 05 Aug 2005 @ 21:06:32 GMT

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

It's hard to give recommendations without knowing any details. Are these ETL or user queries, could OLAP functions eliminate need for volatile tables, are the volatile tables fact data or aggregations of dimension data, are you joining multiple large fact tables or one large fact and multiple lookup tables, are queries hand coded or system generated, etc.

I've always felt that 80% of the tuning opportunities are in the table design, primarily PI selection. If your system was designed by recycled DB2 or Oracle folks you probably have a problem. You also need to make sure you're collecting stats on join and filter columns, but that's not so easy to do in a high volume environment. Before adding nusi's I'd try collecting multicolumn stats. In R4 you couldn't do multicolumn stats so you ended up using nusi's just to get the stats. We found that about 70% of our nusi's weren't actually being used, and they cost you 8+ bytes per row and they're a big hit on load performance.

Instead of MLOAD you may want to consider a fastload to an empty staging table then insert select into the target table. MLOAD prevents you from using USI's and Join Indexes. If the number of rows being inserted is a small percentage of the table total you may get better performance as well. I seem to recall a thumbrule that if it affects more than 10% of the rows then multiload is the better choice. On ETL we've always found multiload deletes to be much faster than BTEQ.

If your queries are generated by a reporting tool look for mismatched data types on join and where columns and functions used in joins. Depending on the organization the folks setting up the meta layer may not know much about query tuning.

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