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
4 1595
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
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
Try converting those sub-queries into a left join
Happy Coding!!!
--- CK
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
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
by: Jason |
last post by:
Does anyone know? Thanks so much.
|
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...
|
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...
|
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
=============...
|
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...
|
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
|
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...
|
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.
...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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...
|
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...
| |