About Qurey Optimization & Tuning

Tools & Utilities
Enthusiast

About Qurey Optimization & Tuning

Hi Friends,

I am new in Query Fine Tuning.... I know query tuning is such vast thing in DB side. We need to fine tune DB level , Business Logic , Physical Level , SQL tuning....

Can any one tel me , how to fine tune & optimize the query ? Suppose lets say if the query is taking such a long time , what are all the approach we need to take care of in our mind???

For example,

Let's say I have Query , In that i am using 7 to 8 tables , the Expected time for this query is 7mins according to the Business. But there is a case , this query is taking such a long time to give the op? I need to fine tune this???

How can i fine tune the qry ( SQL Tuning )

what are all the initial apporach i need to follow up?

how can i find out the Root Causes???

Is our Explain plan is Useful for whole tuning part ????

can any one suggest me.......

4 REPLIES
Teradata Employee

Re: About Qurey Optimization & Tuning

Hello,

Explain is specifically for knowning and tuning the execution plan of the queries. You can play with indexes, skewness, joins, join-indexes and statistics to optimize and fine-tune the queries.

Regards,

Adeel
Enthusiast

Re: About Qurey Optimization & Tuning

Hi thnx for your response... can you just give me overview of what are all the initial check we need to do for tuning.... what are all the approach ????
Teradata Employee

Re: About Qurey Optimization & Tuning

I have already listed the factors involving query optimization. Additionally, the locking factor is also important. For details you can consult the documentation.

There is no pre-defined patterns/steps to optimize a query, it always depends on the scenario.

Regards,

Adeel
Enthusiast

Re: About Qurey Optimization & Tuning

Hi Maran,

You can do the following to tune the query.

1.If the query has joins between 7 or 8 tables then split the query into smaller parts i.e create 2 or 3 volatile tables having half of the joins in one volatile table and the remaining in the other volatile table.And at last you can use these volatile tables to get the final output.
2.Check whether collect stats have been done on all tables used in this query.If not do collect stats.
3.See that the join condition is done on the indexed columns from both the tables.

Cheers:-)