SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need help tuing a sql statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

yougandhar1
Starting Member

USA
7 Posts

Posted - 05/20/2013 :  09:52:07  Show Profile  Reply with Quote
Any suggestions are helpful on how to tune the following statement? Thx


SELECT
PC2CNFDBO.T_CS_PTY_EXT.PTY_ID,
PC2CNFDBO.T_CS_PTY_EXT.CROSS_SRC_ID,
PC2CNFDBO.T_CS_PTY_EXT.PER_SRC_SYS_C,
PC2CNFDBO.T_CS_PTY_EXT.SURVIVOR_I,
PC2CNFDBO.T_CS_POSN_RLTNSHP.POSN_ASGT_ID,
PC2CNFDBO.T_CS_POSN_RLTNSHP.POSN_RLTNSHP_ID,
PC2CNFDBO.T_CS_POSN_RLTNSHP.POSN_RLTNSHP_EFCTV_D,
PC2CNFDBO.T_CS_POSN_RLTNSHP.POSN_RLTNSHP_END_D,
PC2CNFDBO.T_CS_POSN_RLTNSHP.PER_ROLE_C,
PC2CNFDBO.T_CS_POSN_RLTNSHP.RELATED_PTY_ID,
IDNTFCTN_SSN.ID_TY_C TY_SSN,
IDNTFCTN_SSN.ID_VAL RELATED_PTY_SSN,
PC2CNFDBO.T_CS_PTY_NM.FULL_LEGAL_NM,
IDNTFCTN_CORPID.ID_TY_C TY_CORPID,
IDNTFCTN_CORPID.ID_VAL RELATED_TY_CORPID,
PC2CNFDBO.T_CS_PTY_MZ.MZ_REF_S_ID,
PC2CNFDBO.T_CS_MZ_REF_S.MZ_C,
PC2CNFDBO.T_CS_POSN_RLTNSHP.CRE_D,
PC2CNFDBO.T_CS_POSN_RLTNSHP.CRE_BY_BDGE_N_C,
PC2CNFDBO.T_CS_POSN_RLTNSHP.UPD_D,
PC2CNFDBO.T_CS_POSN_RLTNSHP.UPD_BDGE_N_C,
IDNTFCTN_SSN.CRE_D,
IDNTFCTN_SSN.CRE_BY_BDGE_N_C,
IDNTFCTN_SSN.UPD_D,
IDNTFCTN_SSN.UPD_BDGE_N_C,
IDNTFCTN_CORPID.CRE_D,
IDNTFCTN_CORPID.CRE_BY_BDGE_N_C,
IDNTFCTN_CORPID.UPD_D,
IDNTFCTN_CORPID.UPD_BDGE_N_C,
PC2CNFDBO.T_CS_PTY_NM.CRE_D,
PC2CNFDBO.T_CS_PTY_NM.CRE_BY_BDGE_N_C,
PC2CNFDBO.T_CS_PTY_NM.UPD_D,
PC2CNFDBO.T_CS_PTY_NM.UPD_BDGE_N_C,
PC2CNFDBO.T_CS_PTY_MZ.CRE_D,
PC2CNFDBO.T_CS_PTY_MZ.CRE_BY_BDGE_N_C,
PC2CNFDBO.T_CS_PTY_MZ.UPD_D,
PC2CNFDBO.T_CS_PTY_MZ.UPD_BDGE_N_C,
PC2CNFDBO.T_CS_MZ_REF_S.CRE_D,
PC2CNFDBO.T_CS_MZ_REF_S.CRE_BY_BDGE_N_C,
PC2CNFDBO.T_CS_MZ_REF_S.UPD_D,
PC2CNFDBO.T_CS_MZ_REF_S.UPD_BDGE_N_C,
EXT_A.CROSS_SRC_ID RELATED_PERSON_ID,
T_CS_PTY_EXT.FUNC_MGR_NM,
T_CS_PTY_EXT.STFG_REP_NM,
T_CS_PTY_EXT.HR_REP_NM,
T_CS_PTY_EXT.ALT_CNTCT_NM
FROM
PC2CNFDBO.T_CS_PTY_EXT,
PC2CNFDBO.T_CS_PTY_EXT EXT_A,
PC2CNFDBO.T_CS_JOB_POSN,
PC2CNFDBO.T_CS_POSN_RLTNSHP,
PC2CNFDBO.T_CS_PTY_IDNTFCTN IDNTFCTN_SSN,
PC2CNFDBO.T_CS_PTY_IDNTFCTN IDNTFCTN_CORPID,
PC2CNFDBO.T_CS_PTY_NM,
PC2CNFDBO.T_CS_PTY_MZ,
PC2CNFDBO.T_CS_MZ_REF_S
WHERE
T_CS_PTY_EXT.PTY_ID=T_CS_JOB_POSN.PTY_ID
AND T_CS_JOB_POSN.PTY_ID=T_CS_POSN_RLTNSHP.PTY_ID
AND T_CS_JOB_POSN.POSN_ASGT_ID=T_CS_POSN_RLTNSHP.POSN_ASGT_ID
AND EXT_A.PTY_ID=T_CS_POSN_RLTNSHP.RELATED_PTY_ID
AND T_CS_POSN_RLTNSHP.RELATED_PTY_ID=IDNTFCTN_SSN.PTY_ID
AND T_CS_POSN_RLTNSHP.RELATED_PTY_ID=IDNTFCTN_CORPID.PTY_ID
AND T_CS_POSN_RLTNSHP.RELATED_PTY_ID=T_CS_PTY_NM.PTY_ID
AND T_CS_POSN_RLTNSHP.RELATED_PTY_ID=T_CS_PTY_MZ.PTY_ID
AND T_CS_PTY_MZ.MZ_REF_S_ID=T_CS_MZ_REF_S.MZ_REF_S_ID
AND T_CS_PTY_EXT.PTY_EXT_END_D IS NULL
AND T_CS_JOB_POSN.JOB_POSN_END_D IS NULL
AND EXT_A.PTY_EXT_END_D IS NULL
AND T_CS_POSN_RLTNSHP.POSN_RLTNSHP_END_D IS NULL
AND T_CS_POSN_RLTNSHP.POSN_RLTNSHP_STS_C='A'
AND T_CS_PTY_NM.PTY_NM_END_D IS NULL
AND T_CS_PTY_MZ.PTY_MZ_END_D IS NULL
AND T_CS_MZ_REF_S.MZ_REF_S_END_D IS NULL
AND IDNTFCTN_SSN.PTY_IDNTFCTN_END_D IS NULL
AND IDNTFCTN_CORPID.PTY_IDNTFCTN_END_D IS NULL
AND IDNTFCTN_SSN.ID_TY_C='03' AND IDNTFCTN_SSN.CUR_PER_ID_I='Y'
AND IDNTFCTN_CORPID.ID_TY_C='10' AND IDNTFCTN_CORPID.CUR_PER_ID_I='Y'

yougandhar1
Starting Member

USA
7 Posts

Posted - 05/20/2013 :  10:15:05  Show Profile  Reply with Quote
It is takin around 115 seconds to execute,
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 05/20/2013 :  11:52:46  Show Profile  Reply with Quote
We need a lot more information than that. Not to be rude, but that's like asking: my car won't start, why? We have no clue about your table structure, indexes, table size, how the tables even relate nor what you are tryiong to achomplish And to make it more difficult you are using the old join style instad of the newer (15 year old) ANSI style joins.

I know it's hard to see the forest through the trees, but remember we no nothing about your system. So, let's start with the questiosn I asked above. Also, I'd suggest reading the two links below. That will help you provide more detail, and in turn, allow us to hel you better.

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

Ifor
Aged Yak Warrior

583 Posts

Posted - 05/20/2013 :  14:03:47  Show Profile  Reply with Quote
With the limited information provided, you could try using temp tables.

Something like:

SELECT pty_id, cross_src_id, per_src_sys_c, survivor_i, func_mgr_nm, stfg_rep_nm, hr_rep_nm, alt_cntct_nm     
INTO #t_cs_pty_ext
FROM pc2cnfdbo.t_cs_pty_ext
WHERE pty_ext_end_d IS NULL;

CREATE CLUSTERED INDEX temp_t_cs_pty_ext
ON #t_cs_pty_ext(pty_id);

SELECT pty_id, posn_asgt_id
INTO #t_cs_job_posn
FROM pc2cnfdbo.t_cs_job_posn
WHERE job_posn_end_d IS NULL;
 
CREATE CLUSTERED INDEX temp_t_cs_job_posn
ON #t_cs_job_posn(pty_id);

SELECT pty_id, posn_asgt_id
	posn_rltnshp_id, posn_rltnshp_efctv_d, posn_rltnshp_end_d, per_role_c
	,related_pty_id, cre_d, cre_by_bdge_n_c, upd_d, upd_bdge_n_c
INTO #t_cs_posn_rltnshp
FROM pc2cnfdbo.t_cs_posn_rltnshp
WHERE posn_rltnshp_end_d IS NULL
	AND posn_rltnshp_sts_c = 'A';

CREATE CLUSTERED INDEX temp_t_cs_posn_rltnshp
ON #t_cs_posn_rltnshp(pty_id, posn_asgt_id);

SELECT pty_id, id_ty_c,
	id_val, cre_d, cre_by_bdge_n_c, upd_d, upd_bdge_n_c
INTO #t_cs_pty_idntfctn
FROM pc2cnfdbo.t_cs_pty_idntfctn
WHERE pty_idntfctn_end_d IS NULL
	AND id_ty_c IN ('03', '10')
	AND cur_per_id_i = 'Y';

CREATE CLUSTERED INDEX temp_t_cs_pty_idntfctn
ON #t_cs_pty_idntfctn(pty_id, id_ty_c);

SELECT pty_id
	,full_legal_nm, cre_d, cre_by_bdge_n_c, upd_d, upd_bdge_n_c
INTO #t_cs_pty_nm
FROM pc2cnfdbo.t_cs_pty_nm
WHERE pty_nm_end_d IS NULL;

CREATE CLUSTERED INDEX temp_t_cs_pty_nm
ON #t_cs_pty_nm(pty_id);

SELECT pty_id
       ,mz_ref_s_id, cre_d, cre_by_bdge_n_c, upd_d, upd_bdge_n_c
INTO #t_cs_pty_mz
FROM pc2cnfdbo.t_cs_pty_mz
WHERE pty_mz_end_d IS NULL;

CREATE CLUSTERED INDEX temp_t_cs_pty_mz
ON #t_cs_pty_mz(pty_id);

SELECT mz_ref_s_id
	,mz_c, cre_d, cre_by_bdge_n_c, upd_d, upd_bdge_n_c
INTO #t_cs_mz_ref_s
FROM pc2cnfdbo.t_cs_mz_ref_s
WHERE mz_ref_s_end_d IS NULL;

CREATE CLUSTERED INDEX temp_t_cs_mz_ref_s
ON #t_cs_mz_ref_s(mz_ref_s_id);

SELECT A.pty_id, 
       A.cross_src_id, 
       A.per_src_sys_c, 
       A.survivor_i, 
       D.posn_asgt_id, 
       D.posn_rltnshp_id, 
       D.posn_rltnshp_efctv_d, 
       D.posn_rltnshp_end_d, 
       D.per_role_c, 
       D.related_pty_id, 
       E.id_ty_c AS TY_SSN, 
       E.id_val AS RELATED_PTY_SSN, 
       G.full_legal_nm, 
       F.id_ty_c TY_CORPID, 
       F.id_val AS RELATED_TY_CORPID, 
       H.mz_ref_s_id, 
       I.mz_c, 
       D.cre_d, 
       D.cre_by_bdge_n_c, 
       D.upd_d, 
       D.upd_bdge_n_c, 
       E.cre_d, 
       E.cre_by_bdge_n_c, 
       E.upd_d, 
       E.upd_bdge_n_c, 
       F.cre_d, 
       F.cre_by_bdge_n_c, 
       F.upd_d, 
       F.upd_bdge_n_c, 
       G.cre_d, 
       G.cre_by_bdge_n_c, 
       G.upd_d, 
       G.upd_bdge_n_c, 
       H.cre_d, 
       H.cre_by_bdge_n_c, 
       H.upd_d, 
       H.upd_bdge_n_c, 
       I.cre_d, 
       I.cre_by_bdge_n_c, 
       I.upd_d, 
       I.upd_bdge_n_c, 
       B.cross_src_id AS RELATED_PERSON_ID, 
       A.func_mgr_nm, 
       A.stfg_rep_nm, 
       A.hr_rep_nm, 
       A.alt_cntct_nm 
FROM #t_cs_pty_ext A
	JOIN #t_cs_job_posn C
		ON A.pty_id = C.pty_id
	JOIN #t_cs_posn_rltnshp D
		ON A.pty_id = D.pty_id
			AND C.posn_asgt_id = D.posn_asgt_id
	JOIN #t_cs_pty_ext B
		ON B.pty_id = D.related_pty_id
	JOIN #t_cs_pty_idntfctn E
		ON D.related_pty_id = E.pty_id
		   AND E.id_ty_c = '03' 
	JOIN #t_cs_pty_idntfctn F
		ON D.related_pty_id = F.pty_id
		   AND F.id_ty_c = '10' 
	JOIN #t_cs_pty_nm G
		ON D.related_pty_id = G.pty_id
	JOIN #t_cs_pty_mz H
		ON D.related_pty_id = H.pty_id
	JOIN #t_cs_mz_ref_s I
		ON H.mz_ref_s_id = I.mz_ref_s_id

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000