Performance

Database
Enthusiast

Performance

I have a query with around 60 joins ,
the first four joins are inner and the rest all are left outer joins.

Can we improve the performance of the query by changing the order of the left outer joins
used in the query.

Is there any rule which we should follow while putting these outer joins like based on the
size of the tables or any other reasons.

In the Teradata pdf's, its mentioned that the execution will follow the order of the
ON statements ,but also there is a note stating that teradata maynot go with the order of the
joins as specified in the query
1 REPLY

Re: Performance

Hi,
I haven't seen any standart steps that always help, but you can try to do following things:
1) Check statistics, migth be you can add something there. Try: diagnostic helpstats on for session; and after that check explain plan of your query and see the tips in the bottom of explain plan;
2) Check primary keys;
3) Use UNION (ALL)/INTERSECT instead of some INNER JOINs (can help veeery much especially with partitioned tables);
4) Use MINUS instead of LEFT JOIN table AS a ON ... WHERE a.ID IS NULL;
5) Use subselects and aggregate functions/DISTINCT to force optimizator to make the joins in other ways, for example:
SELECT a.a, a.b, ...
FROM tableA a
JOIN tableB b
ON a.ID = b.ID
LEFT JOIN
(
SELECT a1.Param1, a1.Param2, a2.Param1, SUM(c.AMT0
FROM ....
GROUP BY 1,2,3
)
6) Use hardcoded values or pass these values as parametres for partitioned tables.

You can reach VERY big differences with query rewrites in some times.