Interested to Buy Any Domain ? << Click Here >> for more details...
How do you check the performance of Teradata Query and list
down the basic Performance Tuning steps you use?
- 2 Answers
- 13534 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 are the uses of fact table and dimension table in banking project?
What is inner join and outer join?
What is the maximum number of dml can be coded in a multiload script?
What is TPD?
Describe the between keyword in teradata?
Explain the new features of teradata?
What is a sparse index?
Explain the parallel data extension in teradata?
What is multi insert?
What are some commonly used bteq scripts?
What is the difference between union and union all in teradata?
Can we collect statistics on table level?
Highlight the need for Performance Tuning.
what is sysdba and sysdbc ? which has high priority ?
how do you manage the production space. what are the proactive methods you can take ?
