How can one start troubleshooting a query and subsequently provide a solution in Teradata.
I am looking for best practices.
1. How to identify. 2. What to look for in terms of metrics (system & SQL level) 3. What methods are available and which one to use in which scenario. 4. How to replicate or confirm the problem 5. How to fix and test a solution. 6. Last and but not the least "Best practices for writing SQLs, what to consider from performance prespective in Teradata".
The first thing you want to do is to run an explain on the query. This will tell you each step the parsing engine will take when formulating a plan to execute the query. The second step to improve performance would be to collect statistics for any columns in the query that are involved in a join or a filter (where clause). Then run the explain again after collecting statistics and you can see the difference.