Slow Coalesce

UDA
N/A

Slow Coalesce

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

2 REPLIES

Re: Slow Coalesce

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.
N/A

Re: Slow Coalesce

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;