By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,843 Members | 2,311 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,843 IT Pros & Developers. It's quick & easy.

Help need in performance tuning

OraMaster
100+
P: 135
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!!!

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.  ci.ind_cst_key AS q39_key  
  3.  
  4.  ,  dbo.fn_client_ri_ind_district_zone     
  5.    (ixo.ixo_org_cst_key, ixo.ixo_start_date,ixo.ixo_end_date,'District') AS q39_District  -- District    
  6.  , dbo.fn_client_ri_ind_district_zone     
  7.    (ixo.ixo_org_cst_key, ixo.ixo_start_date,ixo.ixo_end_date,'Zone') AS q39_Zone   --Zone    
  8.  , rc.rotary_club as q39_Rotary_Club         --RC    
  9.  , ci.ind_prf_code as q39_Prefix          
  10.  , ci.ind_first_name as q39_Firstname        --Name    
  11.  , ci.ind_mid_name as q39_Middlename    
  12.  , ci.ind_last_name as q39_Lastname    
  13.  , ci.ind_sfx_code as q39_Suffix    
  14.  , ca.adr_line1 as q39_Addressline1         --Address    
  15.  , ca.adr_line2 as q39_Addressline2    
  16.  , ca.adr_line3 as q39_Addressline3    
  17.  , ca.adr_city as q39_City    
  18.  , ca.adr_state as q39_State    
  19.  , ca.adr_intl_province as q39_Province    
  20.  , ca.adr_post_code as q39_Zip    
  21.  , ca.adr_country as q39_Country          --Country    
  22.  ,(select top 1 cph_phn_number_complete    
  23.   from co_customer_x_phone(nolock)Bus    
  24.   where ci.ind_cst_key = Bus.cph_cst_key    
  25.   and Bus.cph_pht_key = (select top 1 pht_key from co_phone_type    
  26.          where pht_delete_flag = 0    
  27.         and pht_code='Business')    --BusinessPhone    
  28.   and Bus.cph_delete_flag = 0 )as q39_Businessphone    
  29.  ,(select top 1 cph_phn_number_complete    
  30.   from co_customer_x_phone(nolock) phn    
  31.   where ci.ind_cst_key = phn.cph_cst_key    
  32.   and phn.cph_pht_key = (select top 1 pht_key from co_phone_type    
  33.            where pht_delete_flag = 0    
  34.           and pht_code='Home')     --HomePhone    
  35.   and phn.cph_delete_flag = 0 )as q39_Homephone    
  36.  ,(select top 1 cph_phn_number_complete    
  37.   from co_customer_x_phone(nolock)cxp    
  38.   where ci.ind_cst_key = cxp.cph_cst_key    
  39.   and cxp.cph_pht_key = (select top 1 pht_key from co_phone_type    
  40.         where pht_delete_flag = 0    
  41.           and pht_code='Mobile')    --Mobile    
  42.   and cxp.cph_delete_flag = 0 )as q39_Mobile    
  43.  , cc.cst_fax_number_complete_dn as q39_Fax       --Fax    
  44.  , lang.r00_language_name as  q39_Language       --Language    
  45.  , dbo.av_begin_of_day(ixo.ixo_start_date) as q39_RITerm_Start_Date    
  46.  , dbo.av_begin_of_day(ixo.ixo_end_date) as q39_RITerm_End_Date    
  47.  ,NULL AS [q39_add_date]        
  48.  ,NULL AS [q39_add_user]    
  49.  ,NULL AS [q39_change_date]    
  50.  ,NULL AS [q39_change_user]    
  51.  ,NULL AS [q39_delete_flag]    
  52.  ,NULL AS [q39_entity_key]      
  53. from co_individual_x_organization ixo (nolock)     
  54.  join ( select cxc.cxc_cst_key_1 as Rotary_Club_key,     
  55.    co.org_name as Rotary_Club    
  56.    from co_customer_x_customer(nolock) cxc     
  57.    join  co_organization(nolock) co on cxc.cxc_cst_key_1 = co.org_cst_key     
  58.     and co.org_ogt_code = (SELECT fws_value FROM fw_system_option (nolock)     
  59.      WHERE fws_key = '5EA4941A-7D50-4E76-BBA3-458AF4083902')  -- Rotary Club  
  60.    join  co_organization(nolock) co1 on cxc.cxc_cst_key_2 = co1.org_cst_key     
  61.     and co1.org_ogt_code = (SELECT fws_value FROM fw_system_option (nolock)     
  62.      WHERE fws_key = '7FBCBCA0-7420-476B-ABD2-0CEE33A1D51E')  --Rotaract Club  
  63.    where cxc.cxc_rlt_code2 = 'Rotaract Sponsored By'    
  64.    and (dbo.client_ri_org_status(co.org_cst_key, co.org_ogt_code, '')= 'Active')     
  65.    and (dbo.client_ri_org_status(co1.org_cst_key,co1.org_ogt_code, '')= 'Active')     
  66.  union    
  67.    select cxc.cxc_cst_key_2 as Rotary_Club_key,    
  68.      co1.org_name as Rotary_Club    
  69.    from co_customer_x_customer(nolock) cxc     
  70.      join  co_organization(nolock) co on cxc.cxc_cst_key_1 = co.org_cst_key     
  71.     and co.org_ogt_code = (SELECT fws_value FROM fw_system_option (nolock)     
  72.      WHERE fws_key = '7FBCBCA0-7420-476B-ABD2-0CEE33A1D51E')  --Rotaract Club  
  73.      join  co_organization(nolock) co1 on cxc.cxc_cst_key_2 = co1.org_cst_key     
  74.     and co1.org_ogt_code = (SELECT fws_value FROM fw_system_option (nolock)     
  75.      WHERE fws_key = '5EA4941A-7D50-4E76-BBA3-458AF4083902')  -- Rotary Club  
  76.    where cxc.cxc_rlt_code = 'Rotaract Sponsored By'    
  77.    and (dbo.client_ri_org_status(co.org_cst_key, co.org_ogt_code, '')= 'Active')     
  78.    and (dbo.client_ri_org_status(co1.org_cst_key,co1.org_ogt_code, '')= 'Active')) rc    
  79.  on ixo.ixo_org_cst_key = rc.Rotary_Club_key and ixo.ixo_rlt_code = 'Club President'     
  80.  join co_individual(nolock) ci on ixo.ixo_ind_cst_key = ci.ind_cst_key    
  81.  join co_individual_ext(nolock)cix on ci.ind_cst_key = cix.ind_cst_key_ext    
  82.  join co_customer(nolock) cc on ci.ind_cst_key = cc.cst_key    
  83.  join co_customer_x_address(nolock) cxa on cc.cst_cxa_key = cxa.cxa_key and cc.cst_key =  cxa.cxa_cst_key     
  84.  join co_address(nolock) ca on cxa.cxa_adr_key = ca.adr_key     
  85.  left outer join client_ri_language_ability(nolock) lg on cix.ind_cst_key_ext = lg.r04_ind_cst_key     
  86.    and cix.ind_r04_key_ext = lg.r04_key    
  87.  left outer join client_ri_language(nolock) lang on lg.r04_r00_key = lang.r00_key    
  88. where ixo.ixo_delete_flag = 0 and ci.ind_delete_flag = 0 and cc.cst_delete_flag = 0     
  89.  and cxa.cxa_delete_flag = 0 and ca.adr_delete_flag = 0     
  90.  and ca.adr_bad_address_flag = 0 and lg.r04_delete_flag = 0
Dec 21 '09 #1
Share this Question
Share on Google+
5 Replies


ck9663
Expert 2.5K+
P: 2,878
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...

Expand|Select|Wrap|Line Numbers
  1.  
  2. select cxc.cxc_cst_key_1 as Rotary_Club_key, co.org_name as Rotary_Club    
  3. from co_customer_x_customer(nolock) cxc     
  4.    join  co_organization(nolock) co on cxc.cxc_cst_key_1 = co.org_cst_key 
  5.    join fw_system_option s1 on co.org_ogt_code = s1.fws_value and s1.fws_key = '5EA4941A-7D50-4E76-BBA3-458AF4083902'
  6.    join  co_organization(nolock) co1 on cxc.cxc_cst_key_2 = co1.org_cst_key  
  7.    join fw_system_option s2 on co1.org_ogt_code = s2.fws_value and s2.fws_key = '7FBCBCA0-7420-476B-ABD2-0CEE33A1D51E'
  8. 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') 
  9. <YOUR UNION GOES HERE>
  10.  
  11.  
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT t1.col, y2.col, y3.col
  3. FROM 
  4. (this is your subquery with union) t1
  5. left join (select distinct col_key from yourtable2 t2 where condition1) y2 on t1.col = y2.col_key 
  6. left join (select distinct col_key from yourtable2 t3 where condition2) y3 on t1.col = y3.col_key 
  7.  
  8.  
That's just a pseudo-code but I hope you get what I mean.

Happy Coding...

--- CK
Dec 21 '09 #2

OraMaster
100+
P: 135
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
Dec 22 '09 #3

ck9663
Expert 2.5K+
P: 2,878
Here's a sample of how to get the BusinessPhone

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. select
  4. cph_phn_number_complete
  5. from co_individual_x_organization ixo 
  6.       <this is where your other joins are>
  7. join co_individual(nolock) ci on ixo.ixo_ind_cst_key = ci.ind_cst_key  
  8. join (
  9.       select cph_cst_key, min(cph_phn_number_complete) as cph_phn_number_complete
  10.       from co_customer_x_phone bus
  11.          join (select top 1 pht_key 
  12.                from co_phone_type 
  13.                where pht_delete_flag = 0 and pht_code='Business'
  14.               ) bp on Bus.cph_pht_key = bp.pht_key and Bus.cph_delete_flag = 0
  15.       group by cph_cst_key
  16.      ) BusinessPhone on ci.ind_cst_key = BusinessPhone.cph_cst_key
  17.  
  18.  
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
Dec 23 '09 #4

OraMaster
100+
P: 135
Hey thanks buddy!!! Its improved performance of my SQL little bit.
Dec 24 '09 #5

ck9663
Expert 2.5K+
P: 2,878
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
Dec 24 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.