How to improve the efficience of Sql query ?

now i want to get results from server tables, but i found it is very slow, for example :

select Coalesce(T1.Name, T2.Name, T3.Name), T1.M1, T2.M2, T3.M3

from T1

full outer join T2

on Coalesce(T1.Name, NULL) = T2.Name

full outer join T3

on Coalesce(T1.Name, T2.Name) = T3.Name

in Tables i have builded index for name, but when every table have 20000 records, the sql above is very slow, is there other method to improve the query speed ?

Thks

You're trying to full outer join three tables and on the join condition is based on computed columns... well that's a query not build for performance...
If you want to improve performance you need to rewrite the query in a way that doesn't uses coalesce on the join condition, you should use tables columns directly and then filter on the where or on the having

Just a final thought: The
Coalesce(T1.Name, NULL) = T2.Name
Coalesce(T1.Name, T2.Name) = T3.Name
conditions may be rewritten as
T2.Name = Coalesce(T1.Name, NULL)
T3.Name = Coalesce(T1.Name, T2.Name)
take a look if this changes the execution plan.
|||

Liu:

Do you filter based on name or are you wanting to process all rows of all tables?

Dave

|||

every one:

i have solved this problem with hash join such as belows:

select Coalesce(T1.Name, T2.Name, T3.Name), T1.M1, T2.M2, T3.M3

from T1

full outer hash join T2

on Coalesce(T1.Name, NULL) = T2.Name

full outer hash join T3

on Coalesce(T1.Name, T2.Name) = T3.Name

in Tables i have builded index for name, but when every table have 20000 records, the sql above is very slow, is there other method to improve the query speed ?

the time to query changes from 33m to 4s

thks