Help need in performance tuning

[OraMaster]

Hi All,

Please suggest how to improve performance of below SQL to me. I tried but it's not showing any improvement. Thanks in advance!!!

SELECT
 ci.ind_cst_key AS q39_key  
  
 ,  dbo.fn_client_ri_ind_district_zone     
   (ixo.ixo_org_cst_key, ixo.ixo_start_date,ixo.ixo_end_date,'District') AS q39_District  -- District    
 , dbo.fn_client_ri_ind_district_zone     
   (ixo.ixo_org_cst_key, ixo.ixo_start_date,ixo.ixo_end_date,'Zone') AS q39_Zone   --Zone    
 , rc.rotary_club as q39_Rotary_Club         --RC    
 , ci.ind_prf_code as q39_Prefix          
 , ci.ind_first_name as q39_Firstname        --Name    
 , ci.ind_mid_name as q39_Middlename    
 , ci.ind_last_name as q39_Lastname    
 , ci.ind_sfx_code as q39_Suffix    
 , ca.adr_line1 as q39_Addressline1         --Address    
 , ca.adr_line2 as q39_Addressline2    
 , ca.adr_line3 as q39_Addressline3    
 , ca.adr_city as q39_City    
 , ca.adr_state as q39_State    
 , ca.adr_intl_province as q39_Province    
 , ca.adr_post_code as q39_Zip    
 , ca.adr_country as q39_Country          --Country    
 ,(select top 1 cph_phn_number_complete    
  from co_customer_x_phone(nolock)Bus    
  where ci.ind_cst_key = Bus.cph_cst_key    
  and Bus.cph_pht_key = (select top 1 pht_key from co_phone_type    
         where pht_delete_flag = 0    
        and pht_code='Business')    --BusinessPhone    
  and Bus.cph_delete_flag = 0 )as q39_Businessphone    
 ,(select top 1 cph_phn_number_complete    
  from co_customer_x_phone(nolock) phn    
  where ci.ind_cst_key = phn.cph_cst_key    
  and phn.cph_pht_key = (select top 1 pht_key from co_phone_type    
           where pht_delete_flag = 0    
          and pht_code='Home')     --HomePhone    
  and phn.cph_delete_flag = 0 )as q39_Homephone    
 ,(select top 1 cph_phn_number_complete    
  from co_customer_x_phone(nolock)cxp    
  where ci.ind_cst_key = cxp.cph_cst_key    
  and cxp.cph_pht_key = (select top 1 pht_key from co_phone_type    
        where pht_delete_flag = 0    
          and pht_code='Mobile')    --Mobile    
  and cxp.cph_delete_flag = 0 )as q39_Mobile    
 , cc.cst_fax_number_complete_dn as q39_Fax       --Fax    
 , lang.r00_language_name as  q39_Language       --Language    
 , dbo.av_begin_of_day(ixo.ixo_start_date) as q39_RITerm_Start_Date    
 , dbo.av_begin_of_day(ixo.ixo_end_date) as q39_RITerm_End_Date    
 ,NULL AS [q39_add_date]        
 ,NULL AS [q39_add_user]    
 ,NULL AS [q39_change_date]    
 ,NULL AS [q39_change_user]    
 ,NULL AS [q39_delete_flag]    
 ,NULL AS [q39_entity_key]      
from co_individual_x_organization ixo (nolock)     
 join ( select cxc.cxc_cst_key_1 as Rotary_Club_key,     
   co.org_name as Rotary_Club    
   from co_customer_x_customer(nolock) cxc     
   join  co_organization(nolock) co on cxc.cxc_cst_key_1 = co.org_cst_key     
    and co.org_ogt_code = (SELECT fws_value FROM fw_system_option (nolock)     
     WHERE fws_key = '5EA4941A-7D50-4E76-BBA3-458AF4083902')  -- Rotary Club  
   join  co_organization(nolock) co1 on cxc.cxc_cst_key_2 = co1.org_cst_key     
    and co1.org_ogt_code = (SELECT fws_value FROM fw_system_option (nolock)     
     WHERE fws_key = '7FBCBCA0-7420-476B-ABD2-0CEE33A1D51E')  --Rotaract Club  
   where cxc.cxc_rlt_code2 = 'Rotaract Sponsored By'    
   and (dbo.client_ri_org_status(co.org_cst_key, co.org_ogt_code, '')= 'Active')     
   and (dbo.client_ri_org_status(co1.org_cst_key,co1.org_ogt_code, '')= 'Active')     
 union    
   select cxc.cxc_cst_key_2 as Rotary_Club_key,    
     co1.org_name as Rotary_Club    
   from co_customer_x_customer(nolock) cxc     
     join  co_organization(nolock) co on cxc.cxc_cst_key_1 = co.org_cst_key     
    and co.org_ogt_code = (SELECT fws_value FROM fw_system_option (nolock)     
     WHERE fws_key = '7FBCBCA0-7420-476B-ABD2-0CEE33A1D51E')  --Rotaract Club  
     join  co_organization(nolock) co1 on cxc.cxc_cst_key_2 = co1.org_cst_key     
    and co1.org_ogt_code = (SELECT fws_value FROM fw_system_option (nolock)     
     WHERE fws_key = '5EA4941A-7D50-4E76-BBA3-458AF4083902')  -- Rotary Club  
   where cxc.cxc_rlt_code = 'Rotaract Sponsored By'    
   and (dbo.client_ri_org_status(co.org_cst_key, co.org_ogt_code, '')= 'Active')     
   and (dbo.client_ri_org_status(co1.org_cst_key,co1.org_ogt_code, '')= 'Active')) rc    
 on ixo.ixo_org_cst_key = rc.Rotary_Club_key and ixo.ixo_rlt_code = 'Club President'     
 join co_individual(nolock) ci on ixo.ixo_ind_cst_key = ci.ind_cst_key    
 join co_individual_ext(nolock)cix on ci.ind_cst_key = cix.ind_cst_key_ext    
 join co_customer(nolock) cc on ci.ind_cst_key = cc.cst_key    
 join co_customer_x_address(nolock) cxa on cc.cst_cxa_key = cxa.cxa_key and cc.cst_key =  cxa.cxa_cst_key     
 join co_address(nolock) ca on cxa.cxa_adr_key = ca.adr_key     
 left outer join client_ri_language_ability(nolock) lg on cix.ind_cst_key_ext = lg.r04_ind_cst_key     
   and cix.ind_r04_key_ext = lg.r04_key    
 left outer join client_ri_language(nolock) lang on lg.r04_r00_key = lang.r00_key    
where ixo.ixo_delete_flag = 0 and ci.ind_delete_flag = 0 and cc.cst_delete_flag = 0     
 and cxa.cxa_delete_flag = 0 and ca.adr_delete_flag = 0     
 and ca.adr_bad_address_flag = 0 and lg.r04_delete_flag = 0

[ck9663]

I didn't read the entire code but it seems your processing your tables row-by-row with all those "select top 1" inside your query. Also, modify your inner subquery by doing a simple join. I modified your first subquery to illustrate what am talking about...

select cxc.cxc_cst_key_1 as Rotary_Club_key, co.org_name as Rotary_Club    
from co_customer_x_customer(nolock) cxc     
   join  co_organization(nolock) co on cxc.cxc_cst_key_1 = co.org_cst_key 
   join fw_system_option s1 on co.org_ogt_code = s1.fws_value and s1.fws_key = '5EA4941A-7D50-4E76-BBA3-458AF4083902'
   join  co_organization(nolock) co1 on cxc.cxc_cst_key_2 = co1.org_cst_key  
   join fw_system_option s2 on co1.org_ogt_code = s2.fws_value and s2.fws_key = '7FBCBCA0-7420-476B-ABD2-0CEE33A1D51E'
where cxc.cxc_rlt_code2 = 'Rotaract Sponsored By' and (dbo.client_ri_org_status(co.org_cst_key, co.org_ogt_code, '')= 'Active')  and (dbo.client_ri_org_status(co1.org_cst_key,co1.org_ogt_code, '')= 'Active') 
<YOUR UNION GOES HERE>

Also your "select top 1" is forcing a row-by-row processing of your tables. I assume it's because you have duplicate on your records? You can use a derived table instead. Something like:

SELECT t1.col, y2.col, y3.col
FROM 
(this is your subquery with union) t1
left join (select distinct col_key from yourtable2 t2 where condition1) y2 on t1.col = y2.col_key 
left join (select distinct col_key from yourtable2 t3 where condition2) y3 on t1.col = y3.col_key

That's just a pseudo-code but I hope you get what I mean.

Happy Coding...

--- CK

[OraMaster]

Thanks!
I tried for the second approch you mentioned but it didn't help me unfortunately.
I didn't get what you are trying to say in you first SQL. Anyways can I use some hints so that this SQL get executed within 10 minutes. Currently it's taking apprx 14 to 18 minutes.
Regds,
Bhushan

[ck9663]

Here's a sample of how to get the BusinessPhone

select
cph_phn_number_complete
from co_individual_x_organization ixo 
      <this is where your other joins are>
join co_individual(nolock) ci on ixo.ixo_ind_cst_key = ci.ind_cst_key  
join (
      select cph_cst_key, min(cph_phn_number_complete) as cph_phn_number_complete
      from co_customer_x_phone bus
         join (select top 1 pht_key 
               from co_phone_type 
               where pht_delete_flag = 0 and pht_code='Business'
              ) bp on Bus.cph_pht_key = bp.pht_key and Bus.cph_delete_flag = 0
      group by cph_cst_key
     ) BusinessPhone on ci.ind_cst_key = BusinessPhone.cph_cst_key

Again, this is a pseudo-code. What am trying to say is you might want to consider using derived table instead of doing a lot of top 1's.

Good Luck!!!

~~ CK

[OraMaster]

Hey thanks buddy!!! Its improved performance of my SQL little bit.

[ck9663]

Try to remove all those subquery with a lot of top 1s. Just use derived tables for most (if not all) of it. Also, make sure you have the right index on the tables. A single table without index could drag your entire query.

Happy Coding!!!

~~ CK