When playing with data and storing a lot of data in tables . Most of the time in the career playing with data , this is an important role that would be played –performance tuning . Also , this is a most important question that would be asked in any interview. High level the steps are to understand where the bottleneck is and try to improve the performance of the query.
The different methods to improve the performance of a query
- Collect Stats of the table
- Create indexes on the columns used on joins
- Run the explain plan and check where the cost is high and try resolving for it.
The third point there are different syntax for different database and below is for Teradata.
DIAGNOSTIC HELPSTATS ON FOR SESSION; EXPLAIN select * table_name;