Effective Teradata Performance Optimization: A Guide to Measuring Improvements

Optimizing Teradata's performance can be a time-consuming and unpredictable task. Therefore, gaining a comprehensive understanding of the optimization areas is advisable before initiating any activities.

As a lead on various performance optimization initiatives over the past decade, I have amassed extensive experience and expertise. I consistently stress the importance of precisely understanding the desired outcomes.

Unclear goals hinder consensus on accomplishments among all participants.

Want more practical data engineering analysis like this?

Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. DWHPro Letters is free. Subscribe to get new issues by email.

Get the next issue

I frequently encountered comments such as "This query lacks speed" or "This report's performance is subpar" throughout my career. It is crucial to avoid implementing an excessively strict performance optimization plan, as it often results in confusion and unproductive debates. As a performance analyst, your responsibility is to identify practical solutions and persuade clients to trust and maintain them throughout the project's progression.

What Measures Should We Use?

What measures do we have? For your client, the only practical measure is likely runtime.

I admit that this ultimately matters.

Get the next issue by email.

In my experience, run times alone are insufficient. It's important to note that multiple projects may run on the same Teradata system, causing run times to vary. Furthermore, extensive workload management options are available on a Teradata system, which could result in intentionally blocking your workload. Consider the potential embarrassment if a report you've improved gets blocked while presenting it to a client.

Prefer Absolute Measures Over Run Times

I prefer to evaluate performance improvements using absolute measures, even when working with a challenging client.

I prefer monitoring CPU usage and disk access. Perhaps concentrating on bottleneck-related metrics, such as utility slots, would be more practical. The optimal approach depends on the specific circumstances.

To prioritize improvement efforts effectively, use absolute measures as evidence, even though run times are also important. Remember that without concrete evidence, you are vulnerable to criticism. Therefore, prioritize absolute measures over run times.

Understand the Big Picture First

Prior to commencing any activities, it is important to grasp the overall concept or main idea.

Focus on tackling the areas that offer the most cost-effective improvement opportunities. Don't allow the client to influence your priorities with statements such as: "This query is slow. You must address it."

Well-intentioned though they may be, such suggestions are unhelpful. Obtain a clear understanding independently, and do not permit the assumptions of others to lead you astray.

Establish Priorities and Resources

Examine every aspect that has potential for optimization, establish a hierarchy of priorities, and outline the corresponding expenses and resources required. Without limitations on costs and resources, opt for the change with the highest priority. Otherwise, make a decision in accordance with the restrictions.

Conclusion: Always establish a precise understanding of the client's expectations and a mutually agreed-upon methodology for assessing progress before embarking on any optimization project. Otherwise, you risk getting bogged down in a continuous cycle of improvement efforts or prolonged debates.

The second installment of the performance optimization series will delve deeper into identifying potential areas of enhancement for a standard Teradata Data Warehouse.

Planning or surviving an enterprise data platform migration?

I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.

Subscribe for free and keep launch access.

Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.