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

How to tune this SQL

OraMaster
100+
P: 135
Hi All,

Below is the detail on this SQL.
Rows returned : 28000
Time taken: 7 to 20 mins

Please let me know whether all joins are correct or not. Also this SQL return mentioned rows with different time as i mentioned. Any kind of suggestion will be deeply appriciated.

Expand|Select|Wrap|Line Numbers
  1. SELECT     co1.org_name AS CSO_Name, coe.org_club_id_ext AS CSO_ID, co1.org_cst_key AS CSO_GUID, co1.org_ogt_code AS CSO_Type, 
  2.                       co2.org_name AS Sponsoring_Rotary_Club_Name, coe2.org_club_id_ext AS Spon_ID, co2.org_cst_key AS SPON_GUID, co3.org_name AS District,
  3.                           (SELECT     cst_sort_name_dn
  4.                             FROM          dbo.co_customer WITH (nolock)
  5.                             WHERE      (cst_key = vcrd.r42_zone_cst_key)) AS Zone, dbo.vw_client_ri_cso_org_status.v03_org_status AS Status, 
  6.                       coe.org_cso_certifydate_ext AS Organization_Date, co1.org_date_founded AS Certification_Date, coe.org_cso_homehospyn_ext AS [Home Host], 
  7.                       coe.org_cso_partnershipprojyn_ext AS [Partnered Club], coe.org_cso_twinclubyn_ext AS [Twin Club], coe.org_cso_clubbase_ext AS Community_School,
  8.                        dbo.fn_client_ri_get_full_address(co1.org_cst_key) AS CSO_Full_Address, dbo.co_address.adr_country AS CSO_Country,
  9.                           (SELECT     r00_language_name
  10.                             FROM          dbo.client_ri_language WITH (nolock)
  11.                             WHERE      (r00_key = coe.org_language_ext)) AS Language
  12. FROM         dbo.co_organization AS co1 WITH (nolock) INNER JOIN
  13.                       dbo.co_customer_x_customer AS cxc1 WITH (nolock) ON co1.org_cst_key = cxc1.cxc_cst_key_2 AND co1.org_ogt_code IN ('RCC', 'Rotaract Club') AND 
  14.                       cxc1.cxc_delete_flag = 0 INNER JOIN
  15.                       dbo.co_organization_ext AS coe WITH (nolock) ON coe.org_cst_key_ext = co1.org_cst_key LEFT OUTER JOIN
  16.                       dbo.client_ri_r42_org_structure_by_date AS vcrd WITH (nolock) ON vcrd.r42_club_cst_key = cxc1.cxc_cst_key_1 AND 
  17.                       (vcrd.r42_end_date >= GETDATE() OR
  18.                       vcrd.r42_end_date IS NULL) AND vcrd.r42_effective_date <= GETDATE() LEFT OUTER JOIN
  19.                       dbo.co_organization AS co2 WITH (nolock) ON vcrd.r42_club_cst_key = co2.org_cst_key LEFT OUTER JOIN
  20.                       dbo.co_organization_ext AS coe2 WITH (nolock) ON coe2.org_cst_key_ext = co2.org_cst_key LEFT OUTER JOIN
  21.                       dbo.co_organization AS co3 WITH (nolock) ON vcrd.r42_district_cst_key = co3.org_cst_key LEFT OUTER JOIN
  22.                       dbo.vw_client_ri_zone AS vrz WITH (nolock) ON vrz.child_orgid = vcrd.r42_district_cst_key LEFT OUTER JOIN
  23.                       dbo.vw_client_ri_cso_org_status ON co1.org_cst_key = dbo.vw_client_ri_cso_org_status.v03_org_cst_key INNER JOIN
  24.                       dbo.co_customer_x_address WITH (nolock) ON dbo.co_customer_x_address.cxa_cst_key = co1.org_cst_key AND 
  25.                       dbo.co_customer_x_address.cxa_delete_flag = 0 LEFT OUTER JOIN
  26.                       dbo.co_address WITH (nolock) ON dbo.co_address.adr_key = dbo.co_customer_x_address.cxa_adr_key AND 
  27.                       co1.org_cst_key = dbo.co_address.adr_cst_key_owner AND dbo.co_address.adr_delete_flag = 0 AND 
  28.                       dbo.co_address.adr_bad_address_flag = 0 INNER JOIN
  29.                       dbo.co_customer AS co WITH (nolock) ON co1.org_cst_key = co.cst_key AND co.cst_cxa_key = dbo.co_customer_x_address.cxa_key
  30. UNION
  31. SELECT     co1.org_name AS CSO_Name, coe.org_club_id_ext AS CSO_ID, co1.org_cst_key AS CSO_GUID, co1.org_ogt_code AS CSO_Type, 
  32.                       co2.org_name AS Sponsoring_Rotary_Club_Name, coe2.org_club_id_ext AS Spon_ID, co2.org_cst_key AS SPON_GUID, co3.org_name AS District,
  33.                           (SELECT     cst_sort_name_dn
  34.                             FROM          dbo.co_customer
  35.                             WHERE      (cst_key = vcrd.r42_zone_cst_key)) AS Zone, dbo.vw_client_ri_cso_org_status.v03_org_status AS Status, 
  36.                       coe.org_cso_certifydate_ext AS Organization_Date, co1.org_date_founded AS Certification_Date, coe.org_cso_homehospyn_ext AS [Home Host], 
  37.                       coe.org_cso_partnershipprojyn_ext AS [Partnered Club], coe.org_cso_twinclubyn_ext AS [Twin Club], coe.org_cso_clubbase_ext AS Community_School,
  38.                        dbo.fn_client_ri_get_full_address(co1.org_cst_key) AS CSO_Full_Address, dbo.co_address.adr_country AS CSO_Country,
  39.                           (SELECT     r00_language_name
  40.                             FROM          dbo.client_ri_language
  41.                             WHERE      (r00_key = coe.org_language_ext)) AS Language
  42. FROM         dbo.co_organization AS co1 WITH (nolock) INNER JOIN
  43.                       dbo.co_customer_x_customer AS cxc1 WITH (nolock) ON co1.org_cst_key = cxc1.cxc_cst_key_1 AND co1.org_ogt_code IN ('RCC', 'Rotaract Club') AND 
  44.                       cxc1.cxc_delete_flag = 0 INNER JOIN
  45.                       dbo.co_organization_ext AS coe WITH (nolock) ON coe.org_cst_key_ext = co1.org_cst_key LEFT OUTER JOIN
  46.                       dbo.client_ri_r42_org_structure_by_date AS vcrd WITH (nolock) ON vcrd.r42_club_cst_key = cxc1.cxc_cst_key_2 AND 
  47.                       (vcrd.r42_end_date >= GETDATE() OR
  48.                       vcrd.r42_end_date IS NULL) AND vcrd.r42_effective_date <= GETDATE() LEFT OUTER JOIN
  49.                       dbo.co_organization AS co2 WITH (nolock) ON vcrd.r42_club_cst_key = co2.org_cst_key LEFT OUTER JOIN
  50.                       dbo.co_organization_ext AS coe2 WITH (nolock) ON coe2.org_cst_key_ext = co2.org_cst_key LEFT OUTER JOIN
  51.                       dbo.co_organization AS co3 WITH (nolock) ON vcrd.r42_district_cst_key = co3.org_cst_key LEFT OUTER JOIN
  52.                       dbo.vw_client_ri_zone AS vrz WITH (nolock) ON vrz.child_orgid = vcrd.r42_district_cst_key LEFT OUTER JOIN
  53.                       dbo.vw_client_ri_cso_org_status ON co1.org_cst_key = dbo.vw_client_ri_cso_org_status.v03_org_cst_key INNER JOIN
  54.                       dbo.co_customer_x_address WITH (nolock) ON dbo.co_customer_x_address.cxa_cst_key = co1.org_cst_key AND 
  55.                       dbo.co_customer_x_address.cxa_delete_flag = 0 LEFT OUTER JOIN
  56.                       dbo.co_address WITH (nolock) ON dbo.co_address.adr_key = dbo.co_customer_x_address.cxa_adr_key AND 
  57.                       co1.org_cst_key = dbo.co_address.adr_cst_key_owner AND dbo.co_address.adr_delete_flag = 0 AND 
  58.                       dbo.co_address.adr_bad_address_flag = 0 INNER JOIN
  59.                       dbo.co_customer AS co WITH (nolock) ON co1.org_cst_key = co.cst_key AND co.cst_cxa_key = dbo.co_customer_x_address.cxa_key
Kindly let me know if you need more information on this.

Kind Regds,
Bhushan
Oct 1 '09 #1
Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
1. Check your indexes.
2. If you don't need to remove duplicate, use UNION ALL instead of UNION (only)
3. You have sub-query resulting to a field. This will result to a ROW-BY-ROW processing, actually. Since this query has to run for every record, consider another technique.
4. Re-arrange your condition:
For AND, place the first condition that will probably return FALSE first.
For OR, place the first condition that will probably return TRUE first.

Good luck!!!


--- CK
Oct 1 '09 #2

OraMaster
100+
P: 135
Hi

Thanks for ur reply!!!

btw i hv removed the union since there was only one difference in where condition of both SQL's as below.
Expand|Select|Wrap|Line Numbers
  1. (co1.org_cst_key = cxc1.cxc_cst_key_1 OR
  2. co1.org_cst_key = cxc1.cxc_cst_key_2)
So my latest SQL is below. I hv also chked wt indexes but still this SQL getting time to execute. Pls provide me some more good suggestion related wt below SQL. thanks in advance

Expand|Select|Wrap|Line Numbers
  1. SELECT     co1.org_name AS CSO_Name, coe.org_club_id_ext AS CSO_ID, co1.org_cst_key AS CSO_GUID, co1.org_ogt_code AS CSO_Type, 
  2. co2.org_name AS Sponsoring_Rotary_Club_Name, coe2.org_club_id_ext AS Spon_ID, co2.org_cst_key AS SPON_GUID, co3.org_name AS District,
  3. (SELECT     cst_sort_name_dn
  4. FROM          dbo.co_customer WITH (nolock)
  5. WHERE      (cst_key = vcrd.r42_zone_cst_key)) AS Zone,
  6. (SELECT     v03_org_status
  7. FROM          dbo.vw_client_ri_cso_org_status(nolock)
  8. WHERE      co1.org_cst_key = v03_org_cst_key) AS Status, coe.org_cso_certifydate_ext AS Organization_Date, 
  9. co1.org_date_founded AS Certification_Date, coe.org_cso_homehospyn_ext AS [Home Host], coe.org_cso_partnershipprojyn_ext AS [Partnered Club], 
  10. coe.org_cso_twinclubyn_ext AS [Twin Club], coe.org_cso_clubbase_ext AS Community_School,
  11. Rtrim(Ltrim(replace(cxa_mailing_label, CASE
  12. (SELECT     cst_type
  13. FROM          co_customer(NOLOCK)
  14. WHERE      cst_key = co1.org_cst_key) WHEN 'Individual' THEN co.cst_ind_full_name_dn ELSE co.cst_org_name_dn END, ''))) CSO_Full_Address, 
  15. dbo.co_address.adr_country AS CSO_Country,
  16. (SELECT     r00_language_name
  17. FROM          dbo.client_ri_language WITH (nolock)
  18. WHERE      (r00_key = coe.org_language_ext)) AS Language
  19. FROM         dbo.co_organization AS co1 WITH (nolock) INNER JOIN
  20. dbo.co_customer_x_customer AS cxc1 WITH (nolock) ON ((co1.org_cst_key = cxc1.cxc_cst_key_1 OR
  21. co1.org_cst_key = cxc1.cxc_cst_key_2) AND co1.org_ogt_code IN ('RCC', 'Rotaract Club') AND cxc1.cxc_delete_flag = 0) INNER JOIN
  22. dbo.co_organization_ext AS coe WITH (nolock) ON coe.org_cst_key_ext = co1.org_cst_key LEFT OUTER JOIN
  23. dbo.client_ri_r42_org_structure_by_date AS vcrd WITH (nolock) ON ((vcrd.r42_club_cst_key = cxc1.cxc_cst_key_2 OR
  24. vcrd.r42_club_cst_key = cxc1.cxc_cst_key_1) AND (vcrd.r42_end_date >= GETDATE() OR
  25. vcrd.r42_end_date IS NULL) AND vcrd.r42_effective_date <= GETDATE()) LEFT OUTER JOIN
  26. dbo.co_organization AS co2 WITH (nolock) ON vcrd.r42_club_cst_key = co2.org_cst_key LEFT OUTER JOIN
  27. dbo.co_organization_ext AS coe2 WITH (nolock) ON coe2.org_cst_key_ext = co2.org_cst_key LEFT OUTER JOIN
  28. dbo.co_organization AS co3 WITH (nolock) ON vcrd.r42_district_cst_key = co3.org_cst_key LEFT OUTER JOIN
  29. dbo.co_customer_x_address WITH (nolock) ON (dbo.co_customer_x_address.cxa_cst_key = co1.org_cst_key AND 
  30. dbo.co_customer_x_address.cxa_delete_flag = 0) LEFT OUTER JOIN
  31. dbo.co_address WITH (nolock) ON (dbo.co_address.adr_key = dbo.co_customer_x_address.cxa_adr_key AND 
  32. co1.org_cst_key = dbo.co_address.adr_cst_key_owner AND dbo.co_address.adr_delete_flag = 0 AND dbo.co_address.adr_bad_address_flag = 0) 
  33. INNER JOIN dbo.co_customer AS co WITH (nolock) ON (co1.org_cst_key = co.cst_key AND co.cst_cxa_key = dbo.co_customer_x_address.cxa_key)
@ck9663
Oct 6 '09 #3

ck9663
Expert 2.5K+
P: 2,878
Try converting those sub-queries into a left join


Happy Coding!!!


--- CK
Oct 7 '09 #4

P: 1
Try this

Expand|Select|Wrap|Line Numbers
  1.   SELECT 
  2.          co1.org_name         AS CSO_Name,
  3.          coe.org_club_id_ext  AS CSO_ID,
  4.          co1.org_cst_key      AS CSO_GUID,
  5.          co1.org_ogt_code     AS CSO_Type,
  6.          co2.org_name         AS Sponsoring_Rotary_Club_Name,
  7.          coe2.org_club_id_ext AS Spon_ID,
  8.          co2.org_cst_key      AS SPON_GUID,
  9.          co3.org_name         AS District,
  10.          cc.cst_sort_name_dn  AS [Zone],
  11.          vcrcos.v03_org_status AS [Status],
  12.          coe.org_cso_certifydate_ext       AS Organization_Date,
  13.          co1.org_date_founded              AS Certification_Date,
  14.          coe.org_cso_homehospyn_ext        AS [Home Host],
  15.          coe.org_cso_partnershipprojyn_ext AS [Partnered Club],
  16.          coe.org_cso_twinclubyn_ext        AS [Twin Club],
  17.          coe.org_cso_clubbase_ext          AS Community_School,
  18.          RTRIM(Ltrim(REPLACE(cxa_mailing_label, CASE cc2.cst_type WHEN 'Individual' THEN co.cst_ind_full_name_dn ELSE co.cst_org_name_dn END, ''))) AS CSO_Full_Address,
  19.          dbo.co_address.adr_country AS CSO_Country,
  20.          crl.r00_language_name AS [Language]
  21.  
  22.   FROM   dbo.co_organization AS co1 WITH (NOLOCK)
  23.  
  24.          --ADDED
  25.          LEFT JOIN co_customer AS cc2 WITH (NOLOCK)
  26.          ON co1.org_cst_key = cc2.cst_key 
  27.  
  28.          --ADDED
  29.          LEFT JOIN dbo.vw_client_ri_cso_org_status as vcrcos WITH (NOLOCK)
  30.          ON co1.org_cst_key = vcrcos.v03_org_cst_key
  31.  
  32.          INNER JOIN dbo.co_customer_x_customer AS cxc1 WITH (NOLOCK)
  33.          ON((co1.org_cst_key = cxc1.cxc_cst_key_1 OR co1.org_cst_key = cxc1.cxc_cst_key_2) 
  34.             AND co1.org_ogt_code IN ('RCC','Rotaract Club') 
  35.             AND cxc1.cxc_delete_flag = 0)
  36.  
  37.          INNER JOIN dbo.co_organization_ext AS coe WITH (NOLOCK)
  38.  
  39.             --ADDED
  40.             LEFT JOIN dbo.client_ri_language AS crl WITH (NOLOCK)
  41.             ON r00_key = coe.org_language_ext
  42.  
  43.          ON     coe.org_cst_key_ext = co1.org_cst_key
  44.  
  45.          LEFT OUTER JOIN dbo.client_ri_r42_org_structure_by_date AS vcrd WITH (NOLOCK)
  46.  
  47.             --ADDED
  48.             LEFT JOIN dbo.co_customer AS cc WITH (NOLOCK)
  49.             ON vcrd.r42_zone_cst_key = cc.cst_key 
  50.  
  51.          ON((vcrd.r42_club_cst_key = cxc1.cxc_cst_key_2 OR vcrd.r42_club_cst_key = cxc1.cxc_cst_key_1)
  52.             AND(vcrd.r42_end_date >= GETDATE() OR vcrd.r42_end_date IS NULL)
  53.             AND vcrd.r42_effective_date <= GETDATE())
  54.  
  55.          LEFT OUTER JOIN dbo.co_organization AS co2 WITH (NOLOCK)
  56.          ON vcrd.r42_club_cst_key = co2.org_cst_key
  57.  
  58.          LEFT OUTER JOIN dbo.co_organization_ext AS coe2 WITH (NOLOCK)
  59.          ON coe2.org_cst_key_ext = co2.org_cst_key
  60.  
  61.          LEFT OUTER JOIN dbo.co_organization AS co3 WITH (NOLOCK)
  62.          ON vcrd.r42_district_cst_key = co3.org_cst_key
  63.  
  64.          LEFT OUTER JOIN dbo.co_customer_x_address WITH (NOLOCK)
  65.          ON dbo.co_customer_x_address.cxa_cst_key = co1.org_cst_key AND dbo.co_customer_x_address.cxa_delete_flag = 0 
  66.  
  67.          LEFT OUTER JOIN dbo.co_address WITH (NOLOCK)
  68.          ON(dbo.co_address.adr_key = dbo.co_customer_x_address.cxa_adr_key
  69.                 AND co1.org_cst_key = dbo.co_address.adr_cst_key_owner
  70.                 AND dbo.co_address.adr_delete_flag = 0
  71.                 AND dbo.co_address.adr_bad_address_flag = 0)
  72.  
  73.          INNER JOIN dbo.co_customer AS co WITH (NOLOCK)
  74.          ON co1.org_cst_key = co.cst_key AND co.cst_cxa_key  = dbo.co_customer_x_address.cxa_key
  75.  
  76.  
- need to see logic probably you may combine cc and cc2
- it is very depend on size of all table which participated in query


i
Oct 16 '09 #5

Post your reply

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