To optimize Teradata SQL performance, it is crucial to identify the root cause of any issues. The SQL statement itself is typically not the culprit but rather one or more stages of the execution plan.
This article does not cover genuine optimization techniques. Instead, it presents a pragmatic method for query optimization. I have noticed that people often rely on a trial and error approach, which may result in a successful outcome through sheer luck, but without a clear understanding of the solution.
We must adopt a detective-like approach to the issue, identifying the incorrect steps in the execution plan and determining their root causes. Isolated inspection is the appropriate method in this case.
We must adopt a detective-like approach to the
Skew and inaccurate cardinality estimates are important causes of slow requests, but not an exhaustive list. Also investigate locks, workload throttling, I/O, CPU pressure, network transfer and competing work.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookThe checklist:
- Take a snapshot of your SQL statement’s EXPLAIN plan, showing any changes in the execution plan caused by your optimization activities.
- Evaluate whether relevant statistics are missing or stale, then measure the effect of collecting them. Low confidence is a diagnostic clue, not proof of a bad plan, and an OR predicate does not universally prevent high-confidence estimates.
- If statistics must be refreshed or added, run another EXPLAIN, and examine whether the execution plan changed. If the optimizer has a new execution plan, rerun your query. The new execution plan could have solved your performance issue.
- If your performance problem did not resolve, it is time to look at each step of the execution plan. While you could start with static analysis on the EXPLAIN output, I prefer to start with real-time monitoring as it is usually the less time-consuming approach. Any real-time monitoring tool (like Viewpoint, PMON, DBMON) gives you the essential information needed.
- Execute your SQL statement and watch each step in real time.
- Compare estimated and actual cardinalities to identify expensive preparation or join decisions. A product join is not inherently wrong; consider input sizes, predicates, duplication and total cost before choosing a rewrite.
- Skewed data: If you detect a step in the execution plan, which is skewed, your base tables have a well-distributing primary index, and the statistics are excellent. A skewed intermediate spool probably causes the problem. Skewed spool happens for several reasons, such as two joined tables being redistributed to a common ROWHASH with only a few distinct values. There is no secret to solving such a problem. Suppose the goal is to avoid the skewed spool. In that case, you should consider all optimization techniques that can help you, like breaking up queries into smaller parts and splitting the query into two parts, one handling the skewed values and the other handling the un-skewed values.
- Repeat the above approach until no wrong steps are left. Don’t forget to set statistics after each impacting change on the SQL statement.
To summarize, optimization consists of the following steps:
Detect the wrong steps in the execution plan, and figure out what is done wrong by the optimizer (bad join, etc.) and why it is wrong.
When you know the reason, you can browse your optimization toolkit for the appropriate tool and use your creativity: Many roads lead to Rome.
Deciding what to change in your data platform?
Compare optimisation, modernisation and migration against your workload and business requirements.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book