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!!! - SELECT
-
ci.ind_cst_key AS q39_key
-
-
, dbo.fn_client_ri_ind_district_zone
-
(ixo.ixo_org_cst_key, ixo.ixo_start_date,ixo.ixo_end_date,'District') AS q39_District -- District
-
, dbo.fn_client_ri_ind_district_zone
-
(ixo.ixo_org_cst_key, ixo.ixo_start_date,ixo.ixo_end_date,'Zone') AS q39_Zone --Zone
-
, rc.rotary_club as q39_Rotary_Club --RC
-
, ci.ind_prf_code as q39_Prefix
-
, ci.ind_first_name as q39_Firstname --Name
-
, ci.ind_mid_name as q39_Middlename
-
, ci.ind_last_name as q39_Lastname
-
, ci.ind_sfx_code as q39_Suffix
-
, ca.adr_line1 as q39_Addressline1 --Address
-
, ca.adr_line2 as q39_Addressline2
-
, ca.adr_line3 as q39_Addressline3
-
, ca.adr_city as q39_City
-
, ca.adr_state as q39_State
-
, ca.adr_intl_province as q39_Province
-
, ca.adr_post_code as q39_Zip
-
, ca.adr_country as q39_Country --Country
-
,(select top 1 cph_phn_number_complete
-
from co_customer_x_phone(nolock)Bus
-
where ci.ind_cst_key = Bus.cph_cst_key
-
and Bus.cph_pht_key = (select top 1 pht_key from co_phone_type
-
where pht_delete_flag = 0
-
and pht_code='Business') --BusinessPhone
-
and Bus.cph_delete_flag = 0 )as q39_Businessphone
-
,(select top 1 cph_phn_number_complete
-
from co_customer_x_phone(nolock) phn
-
where ci.ind_cst_key = phn.cph_cst_key
-
and phn.cph_pht_key = (select top 1 pht_key from co_phone_type
-
where pht_delete_flag = 0
-
and pht_code='Home') --HomePhone
-
and phn.cph_delete_flag = 0 )as q39_Homephone
-
,(select top 1 cph_phn_number_complete
-
from co_customer_x_phone(nolock)cxp
-
where ci.ind_cst_key = cxp.cph_cst_key
-
and cxp.cph_pht_key = (select top 1 pht_key from co_phone_type
-
where pht_delete_flag = 0
-
and pht_code='Mobile') --Mobile
-
and cxp.cph_delete_flag = 0 )as q39_Mobile
-
, cc.cst_fax_number_complete_dn as q39_Fax --Fax
-
, lang.r00_language_name as q39_Language --Language
-
, dbo.av_begin_of_day(ixo.ixo_start_date) as q39_RITerm_Start_Date
-
, dbo.av_begin_of_day(ixo.ixo_end_date) as q39_RITerm_End_Date
-
,NULL AS [q39_add_date]
-
,NULL AS [q39_add_user]
-
,NULL AS [q39_change_date]
-
,NULL AS [q39_change_user]
-
,NULL AS [q39_delete_flag]
-
,NULL AS [q39_entity_key]
-
from co_individual_x_organization ixo (nolock)
-
join ( select cxc.cxc_cst_key_1 as Rotary_Club_key,
-
co.org_name as Rotary_Club
-
from co_customer_x_customer(nolock) cxc
-
join co_organization(nolock) co on cxc.cxc_cst_key_1 = co.org_cst_key
-
and co.org_ogt_code = (SELECT fws_value FROM fw_system_option (nolock)
-
WHERE fws_key = '5EA4941A-7D50-4E76-BBA3-458AF4083902') -- Rotary Club
-
join co_organization(nolock) co1 on cxc.cxc_cst_key_2 = co1.org_cst_key
-
and co1.org_ogt_code = (SELECT fws_value FROM fw_system_option (nolock)
-
WHERE fws_key = '7FBCBCA0-7420-476B-ABD2-0CEE33A1D51E') --Rotaract Club
-
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')
-
union
-
select cxc.cxc_cst_key_2 as Rotary_Club_key,
-
co1.org_name as Rotary_Club
-
from co_customer_x_customer(nolock) cxc
-
join co_organization(nolock) co on cxc.cxc_cst_key_1 = co.org_cst_key
-
and co.org_ogt_code = (SELECT fws_value FROM fw_system_option (nolock)
-
WHERE fws_key = '7FBCBCA0-7420-476B-ABD2-0CEE33A1D51E') --Rotaract Club
-
join co_organization(nolock) co1 on cxc.cxc_cst_key_2 = co1.org_cst_key
-
and co1.org_ogt_code = (SELECT fws_value FROM fw_system_option (nolock)
-
WHERE fws_key = '5EA4941A-7D50-4E76-BBA3-458AF4083902') -- Rotary Club
-
where cxc.cxc_rlt_code = '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')) rc
-
on ixo.ixo_org_cst_key = rc.Rotary_Club_key and ixo.ixo_rlt_code = 'Club President'
-
join co_individual(nolock) ci on ixo.ixo_ind_cst_key = ci.ind_cst_key
-
join co_individual_ext(nolock)cix on ci.ind_cst_key = cix.ind_cst_key_ext
-
join co_customer(nolock) cc on ci.ind_cst_key = cc.cst_key
-
join co_customer_x_address(nolock) cxa on cc.cst_cxa_key = cxa.cxa_key and cc.cst_key = cxa.cxa_cst_key
-
join co_address(nolock) ca on cxa.cxa_adr_key = ca.adr_key
-
left outer join client_ri_language_ability(nolock) lg on cix.ind_cst_key_ext = lg.r04_ind_cst_key
-
and cix.ind_r04_key_ext = lg.r04_key
-
left outer join client_ri_language(nolock) lang on lg.r04_r00_key = lang.r00_key
-
where ixo.ixo_delete_flag = 0 and ci.ind_delete_flag = 0 and cc.cst_delete_flag = 0
-
and cxa.cxa_delete_flag = 0 and ca.adr_delete_flag = 0
-
and ca.adr_bad_address_flag = 0 and lg.r04_delete_flag = 0
5 1731
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... -
-
select cxc.cxc_cst_key_1 as Rotary_Club_key, co.org_name as Rotary_Club
-
from co_customer_x_customer(nolock) cxc
-
join co_organization(nolock) co on cxc.cxc_cst_key_1 = co.org_cst_key
-
join fw_system_option s1 on co.org_ogt_code = s1.fws_value and s1.fws_key = '5EA4941A-7D50-4E76-BBA3-458AF4083902'
-
join co_organization(nolock) co1 on cxc.cxc_cst_key_2 = co1.org_cst_key
-
join fw_system_option s2 on co1.org_ogt_code = s2.fws_value and s2.fws_key = '7FBCBCA0-7420-476B-ABD2-0CEE33A1D51E'
-
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')
-
<YOUR UNION GOES HERE>
-
-
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: -
-
SELECT t1.col, y2.col, y3.col
-
FROM
-
(this is your subquery with union) t1
-
left join (select distinct col_key from yourtable2 t2 where condition1) y2 on t1.col = y2.col_key
-
left join (select distinct col_key from yourtable2 t3 where condition2) y3 on t1.col = y3.col_key
-
-
That's just a pseudo-code but I hope you get what I mean.
Happy Coding...
--- CK
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
Here's a sample of how to get the BusinessPhone -
-
-
select
-
cph_phn_number_complete
-
from co_individual_x_organization ixo
-
<this is where your other joins are>
-
join co_individual(nolock) ci on ixo.ixo_ind_cst_key = ci.ind_cst_key
-
join (
-
select cph_cst_key, min(cph_phn_number_complete) as cph_phn_number_complete
-
from co_customer_x_phone bus
-
join (select top 1 pht_key
-
from co_phone_type
-
where pht_delete_flag = 0 and pht_code='Business'
-
) bp on Bus.cph_pht_key = bp.pht_key and Bus.cph_delete_flag = 0
-
group by cph_cst_key
-
) BusinessPhone on ci.ind_cst_key = BusinessPhone.cph_cst_key
-
-
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
Hey thanks buddy!!! Its improved performance of my SQL little bit.
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: MB |
last post by:
I need to develop a Cold Fusion application using SQL tables, I am not sure
how to setup my tables or that this is the optimal way of setting my tables
for the application that I am trying top...
|
by: pheonix1t |
last post by:
hello,
I've been assigned to do performance tuning on an SQL2000 database
(around 10GB in size, several instances).
So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM.
I see the...
|
by: Fusheng Wang |
last post by:
Hi,
I have an insert intensive database, and it seems that the performance is a
problem.
Any suggestions on performance tuning on insert performance?
Thanks a lot!
Frank
|
by: sacha.prins |
last post by:
Hi,
I read a lot about DB2 INSERT performance here. I have a nice story as
well.
The thing is, I work on 2 installations of DB2 (on completely different
locations) which run on different (but...
|
by: Jeff S |
last post by:
I'm looking for guidance (tutorials, backgrounders, tips, or otherwise) on
measuring the performance of ASP.NET applications. I'm specifically
interested in acquiring the capability of generating...
|
by: Antony Paul |
last post by:
Hi,
I want to get some information on monitoring database activity for
tuning. What are the tables which store performance related details. Is
there any table for open cursors etc..
rgds...
|
by: Xu, Wei |
last post by:
Hi,
I have wrote the following sql sentence.Do you have comments to improve
the performance.I have created all the indexed. But it's still very
slow.Thanks
The primary key is proj_ID and...
|
by: hpw |
last post by:
Hi all,
i'm looking for a good book about .net Performance Tuning.
Things that should be covered by this book:
- Glean information about your program's behavior from profiling tools
-...
|
by: atlaste |
last post by:
Hi,
I'm currently developing an application that uses a lot of
computational power, disk access and memory caching (to be more exact:
an information retrieval platform). In these kind of...
|
by: 73k5blazer |
last post by:
Hello again all..
We have a giant application from a giant software vendor that has very
poor SQL.
It's a PLM CAD application, that makes a call to the db for every cad
node in the assembly.
So...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |