How to tune this SQL

[OraMaster]

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.

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, 
                      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,
                          (SELECT     cst_sort_name_dn
                            FROM          dbo.co_customer WITH (nolock)
                            WHERE      (cst_key = vcrd.r42_zone_cst_key)) AS Zone, dbo.vw_client_ri_cso_org_status.v03_org_status AS Status, 
                      coe.org_cso_certifydate_ext AS Organization_Date, co1.org_date_founded AS Certification_Date, coe.org_cso_homehospyn_ext AS [Home Host], 
                      coe.org_cso_partnershipprojyn_ext AS [Partnered Club], coe.org_cso_twinclubyn_ext AS [Twin Club], coe.org_cso_clubbase_ext AS Community_School,
                       dbo.fn_client_ri_get_full_address(co1.org_cst_key) AS CSO_Full_Address, dbo.co_address.adr_country AS CSO_Country,
                          (SELECT     r00_language_name
                            FROM          dbo.client_ri_language WITH (nolock)
                            WHERE      (r00_key = coe.org_language_ext)) AS Language
FROM         dbo.co_organization AS co1 WITH (nolock) INNER JOIN
                      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 
                      cxc1.cxc_delete_flag = 0 INNER JOIN
                      dbo.co_organization_ext AS coe WITH (nolock) ON coe.org_cst_key_ext = co1.org_cst_key LEFT OUTER JOIN
                      dbo.client_ri_r42_org_structure_by_date AS vcrd WITH (nolock) ON vcrd.r42_club_cst_key = cxc1.cxc_cst_key_1 AND 
                      (vcrd.r42_end_date >= GETDATE() OR
                      vcrd.r42_end_date IS NULL) AND vcrd.r42_effective_date <= GETDATE() LEFT OUTER JOIN
                      dbo.co_organization AS co2 WITH (nolock) ON vcrd.r42_club_cst_key = co2.org_cst_key LEFT OUTER JOIN
                      dbo.co_organization_ext AS coe2 WITH (nolock) ON coe2.org_cst_key_ext = co2.org_cst_key LEFT OUTER JOIN
                      dbo.co_organization AS co3 WITH (nolock) ON vcrd.r42_district_cst_key = co3.org_cst_key LEFT OUTER JOIN
                      dbo.vw_client_ri_zone AS vrz WITH (nolock) ON vrz.child_orgid = vcrd.r42_district_cst_key LEFT OUTER JOIN
                      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
                      dbo.co_customer_x_address WITH (nolock) ON dbo.co_customer_x_address.cxa_cst_key = co1.org_cst_key AND 
                      dbo.co_customer_x_address.cxa_delete_flag = 0 LEFT OUTER JOIN
                      dbo.co_address WITH (nolock) ON dbo.co_address.adr_key = dbo.co_customer_x_address.cxa_adr_key AND 
                      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 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
UNION
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, 
                      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,
                          (SELECT     cst_sort_name_dn
                            FROM          dbo.co_customer
                            WHERE      (cst_key = vcrd.r42_zone_cst_key)) AS Zone, dbo.vw_client_ri_cso_org_status.v03_org_status AS Status, 
                      coe.org_cso_certifydate_ext AS Organization_Date, co1.org_date_founded AS Certification_Date, coe.org_cso_homehospyn_ext AS [Home Host], 
                      coe.org_cso_partnershipprojyn_ext AS [Partnered Club], coe.org_cso_twinclubyn_ext AS [Twin Club], coe.org_cso_clubbase_ext AS Community_School,
                       dbo.fn_client_ri_get_full_address(co1.org_cst_key) AS CSO_Full_Address, dbo.co_address.adr_country AS CSO_Country,
                          (SELECT     r00_language_name
                            FROM          dbo.client_ri_language
                            WHERE      (r00_key = coe.org_language_ext)) AS Language
FROM         dbo.co_organization AS co1 WITH (nolock) INNER JOIN
                      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 
                      cxc1.cxc_delete_flag = 0 INNER JOIN
                      dbo.co_organization_ext AS coe WITH (nolock) ON coe.org_cst_key_ext = co1.org_cst_key LEFT OUTER JOIN
                      dbo.client_ri_r42_org_structure_by_date AS vcrd WITH (nolock) ON vcrd.r42_club_cst_key = cxc1.cxc_cst_key_2 AND 
                      (vcrd.r42_end_date >= GETDATE() OR
                      vcrd.r42_end_date IS NULL) AND vcrd.r42_effective_date <= GETDATE() LEFT OUTER JOIN
                      dbo.co_organization AS co2 WITH (nolock) ON vcrd.r42_club_cst_key = co2.org_cst_key LEFT OUTER JOIN
                      dbo.co_organization_ext AS coe2 WITH (nolock) ON coe2.org_cst_key_ext = co2.org_cst_key LEFT OUTER JOIN
                      dbo.co_organization AS co3 WITH (nolock) ON vcrd.r42_district_cst_key = co3.org_cst_key LEFT OUTER JOIN
                      dbo.vw_client_ri_zone AS vrz WITH (nolock) ON vrz.child_orgid = vcrd.r42_district_cst_key LEFT OUTER JOIN
                      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
                      dbo.co_customer_x_address WITH (nolock) ON dbo.co_customer_x_address.cxa_cst_key = co1.org_cst_key AND 
                      dbo.co_customer_x_address.cxa_delete_flag = 0 LEFT OUTER JOIN
                      dbo.co_address WITH (nolock) ON dbo.co_address.adr_key = dbo.co_customer_x_address.cxa_adr_key AND 
                      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 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

Kindly let me know if you need more information on this.

Kind Regds,
Bhushan

[ck9663]

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

[OraMaster]

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.

(co1.org_cst_key = cxc1.cxc_cst_key_1 OR
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

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, 
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,
(SELECT     cst_sort_name_dn
FROM          dbo.co_customer WITH (nolock)
WHERE      (cst_key = vcrd.r42_zone_cst_key)) AS Zone,
(SELECT     v03_org_status
FROM          dbo.vw_client_ri_cso_org_status(nolock)
WHERE      co1.org_cst_key = v03_org_cst_key) AS Status, coe.org_cso_certifydate_ext AS Organization_Date, 
co1.org_date_founded AS Certification_Date, coe.org_cso_homehospyn_ext AS [Home Host], coe.org_cso_partnershipprojyn_ext AS [Partnered Club], 
coe.org_cso_twinclubyn_ext AS [Twin Club], coe.org_cso_clubbase_ext AS Community_School,
Rtrim(Ltrim(replace(cxa_mailing_label, CASE
(SELECT     cst_type
FROM          co_customer(NOLOCK)
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, 
dbo.co_address.adr_country AS CSO_Country,
(SELECT     r00_language_name
FROM          dbo.client_ri_language WITH (nolock)
WHERE      (r00_key = coe.org_language_ext)) AS Language
FROM         dbo.co_organization AS co1 WITH (nolock) INNER JOIN
dbo.co_customer_x_customer AS cxc1 WITH (nolock) ON ((co1.org_cst_key = cxc1.cxc_cst_key_1 OR
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
dbo.co_organization_ext AS coe WITH (nolock) ON coe.org_cst_key_ext = co1.org_cst_key LEFT OUTER JOIN
dbo.client_ri_r42_org_structure_by_date AS vcrd WITH (nolock) ON ((vcrd.r42_club_cst_key = cxc1.cxc_cst_key_2 OR
vcrd.r42_club_cst_key = cxc1.cxc_cst_key_1) AND (vcrd.r42_end_date >= GETDATE() OR
vcrd.r42_end_date IS NULL) AND vcrd.r42_effective_date <= GETDATE()) LEFT OUTER JOIN
dbo.co_organization AS co2 WITH (nolock) ON vcrd.r42_club_cst_key = co2.org_cst_key LEFT OUTER JOIN
dbo.co_organization_ext AS coe2 WITH (nolock) ON coe2.org_cst_key_ext = co2.org_cst_key LEFT OUTER JOIN
dbo.co_organization AS co3 WITH (nolock) ON vcrd.r42_district_cst_key = co3.org_cst_key LEFT OUTER JOIN
dbo.co_customer_x_address WITH (nolock) ON (dbo.co_customer_x_address.cxa_cst_key = co1.org_cst_key AND 
dbo.co_customer_x_address.cxa_delete_flag = 0) LEFT OUTER JOIN
dbo.co_address WITH (nolock) ON (dbo.co_address.adr_key = dbo.co_customer_x_address.cxa_adr_key AND 
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) 
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

[ck9663]

Try converting those sub-queries into a left join


Happy Coding!!!


--- CK

[bregnev]

Try this

  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,
         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,
         cc.cst_sort_name_dn  AS [Zone],
         vcrcos.v03_org_status AS [Status],
         coe.org_cso_certifydate_ext       AS Organization_Date,
         co1.org_date_founded              AS Certification_Date,
         coe.org_cso_homehospyn_ext        AS [Home Host],
         coe.org_cso_partnershipprojyn_ext AS [Partnered Club],
         coe.org_cso_twinclubyn_ext        AS [Twin Club],
         coe.org_cso_clubbase_ext          AS Community_School,
         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,
         dbo.co_address.adr_country AS CSO_Country,
         crl.r00_language_name AS [Language]
 
  FROM   dbo.co_organization AS co1 WITH (NOLOCK)
 
         --ADDED
         LEFT JOIN co_customer AS cc2 WITH (NOLOCK)
         ON co1.org_cst_key = cc2.cst_key 
 
         --ADDED
         LEFT JOIN dbo.vw_client_ri_cso_org_status as vcrcos WITH (NOLOCK)
         ON co1.org_cst_key = vcrcos.v03_org_cst_key
 
         INNER JOIN dbo.co_customer_x_customer AS cxc1 WITH (NOLOCK)
         ON((co1.org_cst_key = cxc1.cxc_cst_key_1 OR 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 dbo.co_organization_ext AS coe WITH (NOLOCK)
 
            --ADDED
            LEFT JOIN dbo.client_ri_language AS crl WITH (NOLOCK)
            ON r00_key = coe.org_language_ext
 
         ON     coe.org_cst_key_ext = co1.org_cst_key
 
         LEFT OUTER JOIN dbo.client_ri_r42_org_structure_by_date AS vcrd WITH (NOLOCK)
 
            --ADDED
            LEFT JOIN dbo.co_customer AS cc WITH (NOLOCK)
            ON vcrd.r42_zone_cst_key = cc.cst_key 
 
         ON((vcrd.r42_club_cst_key = cxc1.cxc_cst_key_2 OR vcrd.r42_club_cst_key = cxc1.cxc_cst_key_1)
            AND(vcrd.r42_end_date >= GETDATE() OR vcrd.r42_end_date IS NULL)
            AND vcrd.r42_effective_date <= GETDATE())
 
         LEFT OUTER JOIN dbo.co_organization AS co2 WITH (NOLOCK)
         ON vcrd.r42_club_cst_key = co2.org_cst_key
 
         LEFT OUTER JOIN dbo.co_organization_ext AS coe2 WITH (NOLOCK)
         ON coe2.org_cst_key_ext = co2.org_cst_key
 
         LEFT OUTER JOIN dbo.co_organization AS co3 WITH (NOLOCK)
         ON vcrd.r42_district_cst_key = co3.org_cst_key
 
         LEFT OUTER JOIN dbo.co_customer_x_address WITH (NOLOCK)
         ON dbo.co_customer_x_address.cxa_cst_key = co1.org_cst_key AND dbo.co_customer_x_address.cxa_delete_flag = 0 
 
         LEFT OUTER JOIN dbo.co_address WITH (NOLOCK)
         ON(dbo.co_address.adr_key = dbo.co_customer_x_address.cxa_adr_key
                AND 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)
 
         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
 
 

- 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