This chapter discusses the procedure of performance tuning in Teradata.
Explain
The first step in performance tuning is the use of EXPLAIN on your query. EXPLAIN plan gives the details of how optimizer will execute your query. In the Explain plan, check for the keywords like confidence level, join strategy used, spool file size, redistribution, etc.
Collect Statistics
Optimizer uses Data demographics to come up with effective execution strategy. COLLECT STATISTICS command is used to collect data demographics of the table. Make sure that the statistics collected on the columns are up to date.
- Collect statistics on the columns that are used in WHERE clause and on the columns used in the joining condition.
- Collect statistics on the Unique Primary Index columns.
- Collect statistics on Non Unique Secondary Index columns. Optimizer will decide if it can use NUSI or Full Table Scan.
- Collect statistics on the Join Index though the statistics on base table is collected.
- Collect statistics on the partitioning columns.
Data Types
Make sure that proper data types are used. This will avoid the use of excessive storage than required.
Conversion
Make sure that the data types of the columns used in join condition are compatible to avoid explicit data conversions.
Sort
Remove unnecessary ORDER BY clauses unless required.
Spool Space Issue
Spool space error is generated if the query exceeds per AMP spool space limit for that user. Verify the explain plan and identify the step that consumes more spool space. These intermediate queries can be split and put as separately to build temporary tables.
Primary Index
Make sure that the Primary Index is correctly defined for the table. The primary index column should evenly distribute the data and should be frequently used to access the data.
SET Table
If you define a SET table, then the optimizer will check if the record is duplicate for each and every record inserted. To remove the duplicate check condition, you can define Unique Secondary Index for the table.
UPDATE on Large Table
Updating the large table will be time consuming. Instead of updating the table, you can delete the records and insert the records with modified rows.
Dropping Temporary Tables
Drop the temporary tables (staging tables) and volatiles if they are no longer needed. This will free up permanent space and spool space.
MULTISET Table
If you are sure that the input records will not have duplicate records, then you can define the target table as MULTISET table to avoid the duplicate row check used by SET table.