All Teradata Tuning Tips In One Place!

Tuning Topics

This blog post is intended to be a collection of all things that need to be considered when building a Teradata Data Warehouse, the ETL process, as well as SQL queries.

This list is just a start and I hope to get valuable input from my readers to enrich to it over time. I started with just a few ideas and will add additional categories and topics in the next days and weeks.

At the end of this blog post you have the possibility to enter your own ideas. I will add them continuously to this article (I can also add your name if you want).

The Teradata Physical Data Model

Are all Primary Key columns defined as NOT NULL?
Does the Primary Index distribute the rows equally across all AMPs?
Are the Primary Index column values stable or do they change frequently?
Do columns with the same content have the same data type across all tables?
Are SET tables only used for exceptional cases, otherwise always MULTISET tables?
Are tables from which a range of values is often queried row-level partitioned?
Do tables that are often joined together have the same primary index?
Is the primary index of a table often used in a WHERE condition?
Was Multi-Value compression performed on all tables?
Are all row-partitions maintained (to cover new values)?
Is the correct CHARACTER SET used (LATIN vs. UNICODE)?
Is the DATABLOCKSIZE appropriate for the workload (Tactical vs. Strategic)?
Are tables created without the FALLBACK option? (Raluca Topor)
Was a 1-AMP design chosen for tiny tables (1-AMP Hashmap)? (Peter Nagy)
Was columnar considered for wide datamarts with narrow queries? (Peter Nagy)
Was NOT NULL unique key applied on parent tables to enable join elimination? (alternative to soft RI) (Peter Nagy)
Was primary AMP design considered for tables that are frequently reloaded and yet still joined? (Peter Nagy)
Is adequate sample percentage used during collect stats for big tables? (Peter Nagy)
Are statistics copied from table to table whenever its a replica instead of recollected? (Peter Nagy)
Is a value ordered NUSI considered for range query columns on tables where row partitioning is not an option? (Peter Nagy)

The Teradata Statistics

Are full statistics collected on all small tables (fewer table rows than AMPs)?
Are statistics collected on all non-indexed columns that are used in WHERE conditions?
Are full statistics collected on all skewed columns?
Do you use DELETE instead of DROP/CREATE to preserve statistics histories?
Have you collected statistics on all join columns?
Have you collected statistics on all Primary Indexes?
Have you collected statistics on all NUSI?
Have you collected statistics on all partition columns (individually and in combination)?
Are you using Create table Table1 as Table2 with data and stats in order to keep the stats? (Atharva Joshi)

Teradata SQL

Do you use UNION only if you need to remove duplicates, otherwise UNION ALL?
Do you only select the columns you really need?
Do you use outer joins only when it is really necessary?
If possible, do you use WHERE conditions to enable partition elimination?
Do you use multi-statement requests where possible?
Do you use CLOB instead of large VARCHAR for rarely requested columns?
Are you careful not to use functions on join columns?
Are you using a Fast Path INSERT/SELECT?

Please Send Me Your Own Tuning Ideas

* Your email address will not be used for any marketing purposes and it will not be stored.

Roland Wenzlofsky

Roland Wenzlofsky is a graduated computer scientist and Data Warehouse professional working with the Teradata database system for more than 20 years. He is experienced in the fields of banking and telecommunication with a strong focus on performance optimization.