|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/7/2008 11:31:49 AM
Posts: 105,
Visits: 86
|
|
I have a very slow query(generated by microstrategy!!) that joins a very large table to a group of very small volatile tables as follows:
...
join TableA a112
on (coalesce(pa11.Client_id, pa12.Client_id, pa13.Client_id, pa14.Client_id, pa15.Client_id, pa16.Client_id, pa17.Client_id, pa18.Client_id, pa19.Client_id, pa110.Client_id, pa111.Client_id) = a112.Client_id) -- very very slow
Table pa11 has no NULL values and when I run this it is very fast:
...
join PROD_DATA_V.CLt_lu_Client a112
on pa11.Client_id = a112.Client_id -- fast
If I add one table(any table) to the coalesce condition to that list as in this example it becomes very slow again.
...
join PROD_DATA_V.CLt_lu_Client a112
on (coalesce(pa11.Client_id, pa12.Client_id)) = a112.Client_id -- very slow again
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: 10/27/2008 11:53:47 AM
Posts: 50,
Visits: 225
|
|
Could you take a look how the tables pa11, pa12, pa13,pa14,pa15,pa16, pa17, pa18, pa19, pa110 & pa111 are joined before joining to a112 ? I suspect join condition/s are missing and the query becomes a product join.
If everything is fine, you may try with CASE statement instead of COALESCE.
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 3/27/2008 11:31:18 AM
Posts: 19,
Visits: 25
|
|
using function on the join will be an expensive and time consuming. Try to avoid as much as you can.
i would suggest,
try to create a derive/volatile table instead of using coalesce function on the join column.
ex:-
Derive table :
sel a.*
from TABLE1 a
INNER JOIN
( sel coalesce(A1,A2,A3,A4) A1,b.*
from TABLE2 b) b
on a.A1= b.A1;
|
|
|
|