fbpx

7 Essential General Teradata Tuning Tips

By Roland Wenzlofsky

July 3, 2015


Some General Teradata Tuning Ideas

1. One change at a Time

Teradata is a very complex system.  You will often be tempted to change several parameters at the same time to speed up the tuning process. Don’t do it. You might solve the problem but may not know which change fixed the problem. If the problem shows up again, you will have to start over again with your investigations.

2. Keep before and after Explain Plans

Keeping plans aims in the same direction as point 1. Most times, especially when tuning SQL statements, the execution plan changes together with your tuning activities. It’s not satisfying to improve a query’s performance, without knowing what caused the improvement, such as:

  • Is the Optimizer choosing a more suitable join type?
  • Does the Optimizer have better statistics available?
  • Does our change allow the Optimizer to use an alternative access path (secondary index, join index, etc.)

3. Keep a Documentation of all Changes

Another essential task is to keep complete documentation of the changes you did. Probably, these changes have to make it somehow into your production system. Handing over full documentation of changes to the DBA ensures that all your changes will be applied.

4. Solve the Physical Data Model, avoid Workarounds

Workarounds most times are faster to implement but in the end, having a good PDM pays off. As a general rule, fix the problems as early as possible in your ETL/ELT chain, for example:

  • Take care of suitable data types in your PDM, avoid fixing problems in late stages like data marts, reports, etc.
  • Avoid adding logic to a view layer only because you forgot to integrate it into the transform process.
  • Don’t confuse your data warehouse with an operational data store (ODS); you will force the report designers to repeat business logic repeatedly into the reports.

5. Final test your Changes on the Target System

Many aspects of tuning can be tested on any system, but certain details only can be tested on the target system. The target system may surprise you with different execution plans and bottlenecks, such as I/O or CPU bound.

6. Never use Execution Times alone to estimate your Improvements

Absolute measures of CPU time and IOs have to be your primary criteria when evaluating your improvements. Execution times will depend on the overall system workload and many parameters which you can’t improve.

7. Don’t miss the Wood for the Trees

Often, you are assuming bad performance being related to Teradata, but it’s not. Slow running reports could be caused by a slow network connection between Teradata and the reporting server. You have to investigate all related systems, such as ETL-Servers (Datastage, ODI, Informatica, Ab Initio), Reporting Servers, Unix Load Nodes, etc.

__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

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

>