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, it is the execution plan which changes together with your tuning activities. It’s not satisfying to improve the performance of a query, 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 a complete documentation of the changes you did. Probably, these changes have to make it somehow into your production system. Handing over a 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 at 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 over and over again 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 being IO 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.