Optimizing queries
In this recipe, we will show you the basic steps that you can follow to optimize or tune a query. Please be aware that a query can be tuned via multiple methods. This recipe will provide some fundamental steps that can be applied to any query to optimize it as per its identified issue.
A query can show the following issues:
- A product join
- A skewed join
- High CPU/IO steps
- Skewed PI
- Other factors such as using function in joins, joining unmatched columns, and many more
Getting ready
You need to connect to the Teradata system via SQLA or Studio. Identify the query that needs to be optimized.
How to do it...
The following are the steps for optimizing the query:
- List the data objects involved in the query. Do a show on query. Using theÂ
SHOWcommand before the query will display all the objects (tables, views, procs, macros) involved in the query:
**To get all the object in a query** SHOW SELECT USERNAME , tbl.queryid , AcctStringDate , startTime , CAST( (EXTRACT ( HOUR FROM ( ( FirstRespTime...
