All Forums UDA
marcmc 112 posts Joined 12/05
23 Jan 2008
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 a112on (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 slowTable pa11 has no NULL values and when I run this it is very fast:...join PROD_DATA_V.CLt_lu_Client a112on pa11.Client_id = a112.Client_id -- fastIf 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 a112on (coalesce(pa11.Client_id, pa12.Client_id)) = a112.Client_id -- very slow again

Someshnr 53 posts Joined 06/07
23 Jan 2008

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.

sparan 19 posts Joined 05/06
07 Feb 2008

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 aINNER JOIN( sel coalesce(A1,A2,A3,A4) A1,b.*from TABLE2 b) bon a.A1= b.A1;

You must sign in to leave a comment.