Teradata Performance Optimization – Introduction

Roland Wenzlofsky

March 26, 2014

minutes reading time

Teradata Performance optimization can be a very time-consuming task with sometimes unpredictable outcomes. Before starting any optimization activities, it makes sense to get a clear view of the areas subject to optimization.

Being the creative head of several performance optimization projects during the last decade, I gathered a lot of experience and knowledge. I always strongly emphasize the importance of a clear picture of the expected results.

Not defining clear goals makes it difficult or impossible to agree upon achievements with all participants.

In my professional career, I was constantly confronted with statements like “this query is too slow”, “this report does not perform well” .
Believe me. There is no sound basis to set up a severe performance optimization strategy. It always will lead to misunderstandings and endless discussions. As a performance analyst, it is your task to define feasible measures and convince your client to rely on them and stay with them as the project evolves.

But what kind of measures do we have? For your client, probably there exists only one practical measure: run time.

I have to admit that this is what counts at the end of the day.

Still, my experience is that run times are by far not enough. Keep in mind that several projects on the same Teradata System and run times may fluctuate heavily depending on the workload. Even worse, considering the vast workload management possibilities on a Teradata System, your workload could be blocked on purpose, i.e., not running at all. Imagine the disaster if you improved, such as a report, and while you present your achievements to the customer, the reporting SQL gets blocked. I assume this would be pretty embarrassing, right?

Even if it is much more challenging to sell to the client, I prefer to measure performance improvements based on absolute measures.

My favorites are CPU usage and disk access. Maybe bottleneck-related measures make more sense, utility slots. You have to find out what works best in each situation.

Although run times have the highest priority at the end of the day, you will need absolute measures as an argument to continue your improvement efforts. Don’t forget that there are always people in the Teradata arena who would like to see you fail. If you can’t prove your improvements, you are an easy victim. Again: use absolute measures and let run times come afterward.

Before starting any activities:  Get the big picture!

Start working on the areas you assume to bring the most improvement, if feasible, from a cost point of view. Don’t let yourself be steered by the client based on exclamations like: “This query is slow. You should take a look at it”.

Although probably uttered with good intentions, such suggestions are not helpful. Get a clear picture on your own. Don’t let yourself be misled by the assumptions others make.

Analyze each area that could be optimized, create a priority list, and line out the related costs and resources needed. If no cost or resource restrictions apply, go for the highest priority change. Otherwise, decide accordingly.

Conclusion: Never start any optimization task without a clear picture of the client’s expectations and an agreed method to measure improvements. Otherwise, you will end up in discussion or a never-ending loop of improvement attempts.

In the second part of the performance optimization series, I will take a closer look at possible areas of improvement on a typical Teradata Data Warehouse.

  • Avatar
    Nitin Srivastava says:

    Hi Roland

    I strongly agree with you here.
    The statement “Query running very slow” can be misleading. I have seen cases where the query was tuned and when executed it took more time than before.
    However, when I checked DBQL logs, I learned there was a reduction of AMPCPU utilization by 30%. The real reason behind more time was the DelayTime. The query was in Delay which actually gave the impression to the client as if it is running and taking more time than before.


  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like