Teradata performance tuning

We already finished with the SQL tuning part , where the resource gain was in the order of k USD projected onto a 1 year operation within a weeks project. If some of them matches, we will get off the deep understanding the query. This is the most rare situation, forget about. SQL join types, eg.: Later on can the others come into focus.

Uploader: Malarisar
Date Added: 19 February 2009
File Size: 23.34 Mb
Operating Systems: Windows NT/2000/XP/2003/2003/7/8/10 MacOS 10/X
Downloads: 95412
Price: Free* [*Free Regsitration Required]

Teradata Wiki: A to Z Performance Tuning

This is the most rare situation, forget about. Teradata performance optimization basics Define the scope choose the target queries Find the problem causes of a query using PTA. We have precisely the same table structuresit is a must take care of it, it is a part of the concept of not just the testing, but the optimization either!

Pick individual SQLs and optimize its execution This approach finds the teradafa resource consumer SQL statements and reduces the resource consumption, while produces the same results.

Current goals First of all, we got to make clear the business requirement of the project. A performance optimization is the same as a regular functional development on the Data Warehouse.

Tuhing the spool limit will not solve the problems in the most cases. By ap28 Aug Tagged: But you are responsible to define the values to be compressed. This will avoid the use of excessive storage than required. I use PTA and Excel to this.

This level checks if the executed processes are really needed, and they do what the user really needs. No more perfodmance space scenarios. At the end it is important to have a exact, detailed specification of what to do with the SQLs.

These textually differing queries are the same, from the aspect of regularity.

If you create many, and even with skewed distribution, you can stuff your spool up. Data distribution, random-amp-stats, other settings can change between the testing and the go-live. This time we got to finish the load process of a specific data area in the Data Warehouse 1 perfrmance earlier than before.

Teradata - Performance Tuning

The bigger is the number, the better is the loading performance, but the longer is the logon time. As a good rule of thumb, choose no more than SQLs for a project depending on your time and cost limitationsand divide them into delivery drops scripts into each. Do not mix up with cross join Cartesian productwhich is one type of SQL joins. Need to re-write delete statement. Make sure that the statistics collected on the columns are up to date.

It spectacularly displays which execution step falls in the problems above. When we know what to do with each of the SQLs, we can assign them to delivery drops, negotiated with operations and developers.

The next post is about a Teradata compress optimization project. Analyze user requests and system activity, then refine them, eliminating unnecessary activities. There is no exact relationship between high spool usage and ineffective query, but statistically the correlation is high. If the optimizer thinks that a table is pretty small, but it is not indeed, it may choose a product join in all good faith, misleaded by a stale or missing statistics.

There will be some statistics missing.

Later on can the others come into focus. Leave a Reply Cancel reply Your email address will tuinng be published. Spool space error is generated if the query exceeds per AMP spool space limit for that user.

The efficient utilization of its resources is essential, from both financial and user satisfaction aspects. Collect statistics on the columns that are used in WHERE clause and on the columns used in the joining condition. By googinup11 Jul Tagged:

1 thoughts on “Teradata performance tuning”

Leave a Reply

Your email address will not be published. Required fields are marked *