How do you check the performance of Teradata Query and list
down the basic Performance Tuning steps you use?
- 2 Answers
- 12714 Views
- IBM, I also Faced
- E-Mail Answers
Answer Posted / hanumanth
APPROACHES
A. In case of product join scenarios,check for
- Proper usage of alias
- joining on matching columns
- Usage of join keywords - like specifying type of joins
(ex. inner or outer )
- use union in case of "OR” scenarios
- Ensure statistics are collected on join columns and this
is especially important if the columns you are joining on
are not unique.
B. collects stats
- Run command "diagnostic help stats on for the session"
- Gather information on columns on which stats has to be
collected
- Collect stats on suggestions columns
- Also check for stats missing on PI, SI or columns used in
joins - "help stats <databasename>.<tablename>
- Make sure stats are re-collected when at-least 10% of data
changes
- remove unwanted stats or stat which hardly improves
performance of the queries
- Collect stats on columns instead of indexes since index
dropped will drop stats as well!!
- collect stats on index having multiple columns, this might
be helpful when these columns are used in join conditions
- Check if stats are re-created for tables whose structures
have some changes
c. Full table scan scenarios
- Try to avoid FTS scenarios as, it might take very long
time to access all the data in every amp in the system
- Make sure SI is defined on the columns which are used as
part of joins or Alternate access path.
- Collect stats on SI columns else there are chances where
optimizer might go for FTS even when SI is defined on that
particular column
2. If intermediate tables are used to store results, make
sure that
- It has same PI of source and destination table
3. Tune to get the optimizer to join on the Primary Index of
the largest table, when possible, to ensure that the large
table is not redistributed on AMPS
4. For large list of values, avoid using IN /NOT IN in SQLs.
Write large list values to a temporary table and use this
table in the query
5. Make sure when to use exists/not exists condition since
they ignore unknown comparisons (ex. - NULL value in the
column results in unknown) . Hence this leads to
inconsistent results
6. Inner Vs Outer Joins
Check which join works efficiently in given scenarios.Some
examples are
- Outer joins can be used in case of large table joining
with small tables (like fact table joining with Dimension
table based on reference column)
- Inner joins can be used when we get actual data and no
extra data is loaded into spool for processing
Please note for outer join conditions:
1. Filter condition for inner table should be present in
"ON" condition
2. Filter condition for outer table should be present in
"WHERE" condition
| Is This Answer Correct ? | 4 Yes | 1 No |
Post New Answer View All Answers
What is teradata?
If a Node is busy what are the steps you can take to avoid ?
What is a clique?
How you will check the version of teradata?
What are the frequently used data types in teradata?
Highlight a few of the important components of Teradata?
Hi, If anyone has TD 14 Basics dumps or study materials, please share. nirmaaal1991@gmail.com
What do you mean by caching in teradata?
How do you verify a complicated sql?
Give the sizes of SMALLINT, BYTEINT and INTEGER.
What are the joins in teradata?
How do you define Teradata?
Can we collect statistics on table level?
Highlight the need for Performance Tuning.
What are the commands to make a new table, change a table and remove a table in teradata?
