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 1744 ck9663 2,878
Recognized Expert Specialist
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
ck9663 2,878
Recognized Expert Specialist
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.
ck9663 2,878
Recognized Expert Specialist
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 automate.
I've first created 2 tables as follows:
SOSC
-----
1 Record ID (Primary key)
2 CMP_TIME_ID (Foreign key)
|
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 raid5 as a bottleneck. I'd setup a raid 10 and seperate the
logs, database and OS(win2k).
The one thing that was a bit odd to me was that I was told this place
doesn't use indexes. The company is a house builder. They are pretty
|
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 same generation) hardware.
Benchmarking the disk throughput and CPU basically amounts to the same
figures (+/- 10%).
|
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 objective data that
quantifies the effects of performance tuning efforts (e.g., performance
measures before and after implementing caching). I want to be able to
demonstrate cases where performance tuning efforts resulted in measurably
faster...
| |
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
Antony Paul
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
|
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 Task_UID.
SELECT PR.PROJ_NAME AS PRName, PR.PROJ_ID As PRProjID, PR.TASK_UID As
PRTaskUID, 'Dev' AS GroupType,
Feat.PROJ_ID As FeatProjID, Feat.TASK_UID As FeatTaskUID, Feat.FeatureID AS
FeatureID,
|
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
- Identify bottlenecks before tuning
- Optimization when dealing with objects
- Optimize I/O behavior
|
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 applications the
last thing that remains is bare performance tuning.
So for example, you can do an 'if then else' on a bit like a 'case/
switch', an 'if/then/else' and as a multiplication with a static
buffer. Or, you can do sorting with an inline...
|
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 for an assembly with many parts, from remote locations, the
performance goes out the window.
Locally, it all works fine because network latency is <1ms. But some
remote sites we have (that are growing) latency can be as high as
80-100ms.
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |