fbpx

All Teradata Tuning Tips In One Place!

Author Image

By Roland Wenzlofsky

January 3, 2020

Access Paths
Tuning Topics
The Teradata Physical Data Model
The Teradata Statistics
Teradata SQL

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

All Teradata Tuning Tips In One Place! 1
  • 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

All Teradata Tuning Tips In One Place! 14
  • 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

teradata union
  • 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.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
 Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
 Next Article
Buy the Book Teradata Query Performance Tuning
Share0
Tweet0
Share0
Author Image

Roland Wenzlofsky

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

Access Paths

Teradata Join Index – Tuning of Relationship Tables

Architecture

Teradata versus SQL Server – Tuning Considerations

Access Paths

The Teradata MAPS Architecture – Great Performance with All Rows on One AMP

DWH Design

Teradata Identity Columns to achieve better Performance

>