473,322 Members | 1,431 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

How to tune this SQL

OraMaster
135 100+
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
4 1595
ck9663
2,878 Expert 2GB
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
135 100+
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
2,878 Expert 2GB
Try converting those sub-queries into a left join


Happy Coding!!!


--- CK
Oct 7 '09 #4
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

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

Similar topics

1
by: C. Alexander | last post by:
Hey all. I have a whiteboard type program. I have 2 picturebox's (needed for their mousemove/mousedown events) Picture1.Picture = MyPicture ' Here is my hidden picture used to replace (on...
0
by: Joan MacEachern | last post by:
Join Sun's Chief Strategy Officer, Mark Tolliver on Wednesday, December 3, 2003 at 8 am PST for Sun's Network Computing O3-Q4 Event. This event's focus includes product releases that will help you...
3
by: Jason | last post by:
Does anyone know? Thanks so much.
0
by: norton | last post by:
Dear All, our team is writing a large appz and now we takes minutes to compile the source code. May i ask if there are any method to speed up the compile process? (any settings to tell vs.net...
10
by: | last post by:
I am trying to improve the robustness and elegance of my parametized sql statements in ASP 3.0 as they get passed to the sql server SP. Could anyone tell me if there are weaknessess in the way I...
1
by: gilgantic | last post by:
HELP!!! I am trying to fine tune or rewrite my SELECT statement which has a combination of SUM and CASE statements. The values are accurate, but the query is slow. BUSINESS RULE =============...
3
by: Hans Horn | last post by:
Howdy, is there a way to speed-up or tune the database creation? On my laptop (DB2-UDB PE) it takes 2 minutes to create a virgin database. This virgin, btw. is 30MBytes heavy. Is there a way to...
6
by: kvsnramesh | last post by:
hi, I have a problem asked by one of my senior person and finding the answer . What is the step by step procedure for tune a large sql query. OR how do we tune a large SQL query with somany joins
8
by: rshivaraman | last post by:
Hi : I have a TableA with around 10 columns with varchar and numeric datatypes It has 500 million records and its size is 999999999 KB. i believe it is kb i got this data after running...
3
by: travolta001 | last post by:
Speed up your system, repair registry base, uninstall hidden windows components and get rid of boring errors with powerful tips and tricks. Very useful tips from other good sites. ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.