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 simultaneously to speed up the tuning process. Please don’t do it. You might solve the problem but may not know which change fixed the problem. If the problem appears 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. The execution plan changes with your tuning activities, especially when tuning SQL statements. 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 made. Probably, these changes have to make it somehow into your production system. Handing over full documentation of modifications to the DBA ensures that they will apply all your changes.
4. Solve the Physical Data Model, avoid Workarounds
Workarounds 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, and avoid fixing late-stage problems 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 in the reports.
5. Final test your Changes on the Target System
We can test many tuning aspects on any system, but we can only try specific details 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 you can’t improve.
7. Don’t miss the Wood for the Trees
Often, you assume bad performance is 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 must investigate all related systems, such as ETL-Servers (Datastage, ODI, Informatica, Ab Initio), Reporting Servers, Unix Load Nodes, etc.