1. Run an EXPLAIN. Put the word EXPLAIN as the first line of your query. Check the resulting explanation of what teradata will do to run your query and the timings of each step. As you make needed changes to the query, rerun the EXPLAIN and compare to the previous results. Things to check into are excessive run time estimates (** means to large to hold in the time field – very bad), float and data conversions, product joins with long run time estimates, etc.
2. Use GROUP BY instead of DISTINCT on a select or count, especially on a really non-linear column. The DISTINCT is much more resource intensive than the GROUP BY.
3. When you create a table that is to be used to join to another table, make sure the like columns have the same Data Type and length. It is extremely inefficient to join a numeric column to a character column.
4. If you are joining a temp table you created to an existing table, it may help to have the primary index of the temp table the same as the primary index of the table you a joining to. The primary index determines how the data is split across the amps.
5. Check for current statistics on the columns used in the where clause and the join conditions.