asked on

microsoft

Hi Experts,
when we ran the job with Query 1 , the job finished in 3 Hrs 40 Mins.
and when we ran with Query 2 , the job finished in 5 Hrs and 10 Mins.
Differnece b/w query 1 and 2 is highlighted in Blue. Couls you help us in tuning this.
Query 1:

SELECT INST_KEY, ANTAL_KONTI, ANTAL_BTO_NYUDLAAN,
ANTAL_FORTIDINDFRI, BTO_NYUDLAAN, FORTIDINDFRI,
A.INDEKS_FAKTOR_H,
A.INDEKS_FAKTOR_K,
ORDINAERE_AFDRAG, UDBYTTE,
SIKKERHED_BELOEB, RENTER, BIDRAG_ULTIMO, EFF_RENTE_FORR_OMF, EFF_RENTE_ULTIMO,
AAO_belob AS AAO_BELOEB,
FORR_OMFANG, GNS_VAL, PAA_LOEBNE_RENTER, A.NOM_RENTE_SATS,
PRIMO, BOERSKURS_REG, NETTO_TRANS, TAB, VALUTA_REG, REKLASS,
VAERDI_REG, ULTIMO, PRIMO_MV, REKLASS_MV, TAB_MV,
VALUTA_REG_MV, NETTO_TRANS_MV, ULTIMO_MV, BOERSKURS_REG_MV,
PRIMO_NOM, NETTO_TRANS_NOM, REKLASS_NOM, TAB_NOM, VALUTA_REG_NOM,
ULTIMO_NOM, PRIMO_ANTAL, NETTO_TRANS_ANTAL, ULTIMO_ANTAL,
A.EFF_DT, KTTP,
LOAN_PURPOSE, IDKT_START_DT, REDEEMABLE_CODE,
NOM_INDEKS_MK, RENTE_SKIFT_DT, MDR_TIL_RENTESKIFT, STEPUP_DT, UDLOEB_DT,
A.INTERNET_ACCESS_MK,
ULTIMO_OPRVAL_MV, ULTIMO_OPRVAL_NOM, PRIMO_OPRVAL_MV,
PRIMO_OPRVAL_NOM, AFDRAG_DT, AFDRAG_DT_MK, NET_OPRVAL_NOM,
NET_OPRVAL_NOM_MK, NET_OPRVAL_MV, NET_OPRVAL_MV_MK, TAB_OPRVAL_NOM,
TAB_OPRVAL_MV, B_REG_OPRVAL, B_REG_OPRVAL_MK, VAL_REG_OPR_MK, NET_TRANS_JUST_MV, NET_TRANS_JUST_NOM, NET_TRANS_JUST,
ULTIMO_KORR_MV, ULTIMO_KORR_NOM, VAL_KURS_PRIMO,
VAL_KURS_PRIMO_MK, VAL_KURS_ULTIMO, VAL_KURS_ULTIMO_MK,
SEKTOR_INFO, CORR_MK, CORR_UPDATED_MK, VAL_KURS_GNS, REPORTING_DT, ONE_DAY_MAT_MK, ULTIMO_OPRVAL,
LTrim(RTrim(FULLPDST)) AS FULLPDST,
afdrag_opr, b_reg_oprval_mv, b_reg_oprval_mv_mk,
deriv_type, indeks_mk, koeb_salg_mk, new_business_cause,
payment_mk, position, ppstm_papir_type, uafviklet_mk, ultimo_korr,
used_in_repo_mk, val_reg_opr, val_reg_opr_mv_mk, val_reg_opr_mv,
LTrim(RTrim(A.IDKT)) AS IDKT,
LTrim(RTrim(VALUTA)) AS VALUTA,
LTrim(RTrim(AMOUNT_TYPE)) AS AMOUNT_TYPE,
LTrim(RTrim(EXT_CONV_MK)) AS EXT_CONV_MK,
LTrim(RTrim(NEW_BUS_MK)) AS NEW_BUS_MK,
LTrim(RTrim(KNID)) AS KNID,
LTrim(RTrim(REG_NO)) AS REG_NO,
opts_acc,
opts_cust ,
SURR_ID,
LTrim(RTrim(BRANCHE)) AS BRANCHE ,
LTrim(RTrim(CVR)) AS CVR,
LTrim(RTrim(KOMMUNE)) AS KOMMUNE,
LTrim(RTrim(LAND)) AS LAND,
SubString(LTrim(RTrim(LAND_M)), 1,2) AS LAND_M,
LTrim(RTrim(SEKTOR)) AS SEKTOR ,
LTrim(RTrim(SEKTOR_2)) AS SEKTOR_2 ,
LTrim(RTrim(SEKTOR_M1)) AS SEKTOR_M1,
LTrim(RTrim(KNGR)) AS KNGR,
LTrim(RTrim(A.FAST_RENTE_MK)) AS FAST_RENTE_MK,
LTrim(RTrim(A.SIKKERHED_KD)) AS SIKKERHED_KD ,
EXCHANGE_M_FACTOR,
B.SURR_ID_ACC AS SURR_ID_ACC ,
(select COUNT(*) from #ps_SourceTables.DataBase#.#ps_SourceTables.Schema#.#p_SourceTableName# where LoadStatusId = #p_LoadStatusId_src# and SURR_ID = a.surr_id) as numb
FROM #ps_SourceTables.DataBase#.#ps_SourceTables.Schema#.#p_SourceTableName# A
INNER JOIN
#ps_SourceTables.DataBase#.#ps_SourceTables.Schema#.BOD_ACC_DIM B
ON
A.IDKT = B.IDKT and A.opts_acc = B.EFF_DT
WHERE
A.LoadStatusId = #p_LoadStatusId_src#

Query 2 :

SELECT INST_KEY, ANTAL_KONTI, ANTAL_BTO_NYUDLAAN,
ANTAL_FORTIDINDFRI, BTO_NYUDLAAN, FORTIDINDFRI,
A.INDEKS_FAKTOR_H,
A.INDEKS_FAKTOR_K,
ORDINAERE_AFDRAG, UDBYTTE,
SIKKERHED_BELOEB, RENTER, BIDRAG_ULTIMO, EFF_RENTE_FORR_OMF, EFF_RENTE_ULTIMO,
AAO_belob AS AAO_BELOEB,
FORR_OMFANG, GNS_VAL, PAA_LOEBNE_RENTER, A.NOM_RENTE_SATS,
PRIMO, BOERSKURS_REG, NETTO_TRANS, TAB, VALUTA_REG, REKLASS,
VAERDI_REG, ULTIMO, PRIMO_MV, REKLASS_MV, TAB_MV,
VALUTA_REG_MV, NETTO_TRANS_MV, ULTIMO_MV, BOERSKURS_REG_MV,
PRIMO_NOM, NETTO_TRANS_NOM, REKLASS_NOM, TAB_NOM, VALUTA_REG_NOM,
ULTIMO_NOM, PRIMO_ANTAL, NETTO_TRANS_ANTAL, ULTIMO_ANTAL,
A.EFF_DT, KTTP,
LOAN_PURPOSE, IDKT_START_DT, REDEEMABLE_CODE,
NOM_INDEKS_MK, RENTE_SKIFT_DT, MDR_TIL_RENTESKIFT, STEPUP_DT, UDLOEB_DT,
A.INTERNET_ACCESS_MK,
ULTIMO_OPRVAL_MV, ULTIMO_OPRVAL_NOM, PRIMO_OPRVAL_MV,
PRIMO_OPRVAL_NOM, AFDRAG_DT, AFDRAG_DT_MK, NET_OPRVAL_NOM,
NET_OPRVAL_NOM_MK, NET_OPRVAL_MV, NET_OPRVAL_MV_MK, TAB_OPRVAL_NOM,
TAB_OPRVAL_MV, B_REG_OPRVAL, B_REG_OPRVAL_MK, VAL_REG_OPR_MK, NET_TRANS_JUST_MV, NET_TRANS_JUST_NOM, NET_TRANS_JUST,
ULTIMO_KORR_MV, ULTIMO_KORR_NOM, VAL_KURS_PRIMO,
VAL_KURS_PRIMO_MK, VAL_KURS_ULTIMO, VAL_KURS_ULTIMO_MK,
SEKTOR_INFO, CORR_MK, CORR_UPDATED_MK, VAL_KURS_GNS, REPORTING_DT, ONE_DAY_MAT_MK, ULTIMO_OPRVAL,
LTrim(RTrim(FULLPDST)) AS FULLPDST,
afdrag_opr, b_reg_oprval_mv, b_reg_oprval_mv_mk,
deriv_type, indeks_mk, koeb_salg_mk, new_business_cause,
payment_mk, position, ppstm_papir_type, uafviklet_mk, ultimo_korr,
used_in_repo_mk, val_reg_opr, val_reg_opr_mv_mk, val_reg_opr_mv,
LTrim(RTrim(A.IDKT)) AS IDKT,
LTrim(RTrim(VALUTA)) AS VALUTA,
LTrim(RTrim(AMOUNT_TYPE)) AS AMOUNT_TYPE,
LTrim(RTrim(EXT_CONV_MK)) AS EXT_CONV_MK,
LTrim(RTrim(NEW_BUS_MK)) AS NEW_BUS_MK,
LTrim(RTrim(KNID)) AS KNID,
LTrim(RTrim(REG_NO)) AS REG_NO,
opts_acc,
opts_cust ,
SURR_ID,
LTrim(RTrim(BRANCHE)) AS BRANCHE ,
LTrim(RTrim(CVR)) AS CVR,
LTrim(RTrim(KOMMUNE)) AS KOMMUNE,
LTrim(RTrim(LAND)) AS LAND,
SubString(LTrim(RTrim(LAND_M)), 1,2) AS LAND_M,
LTrim(RTrim(SEKTOR)) AS SEKTOR ,
LTrim(RTrim(SEKTOR_2)) AS SEKTOR_2 ,
LTrim(RTrim(SEKTOR_M1)) AS SEKTOR_M1,
LTrim(RTrim(KNGR)) AS KNGR,
LTrim(RTrim(A.FAST_RENTE_MK)) AS FAST_RENTE_MK,
LTrim(RTrim(A.SIKKERHED_KD)) AS SIKKERHED_KD ,
EXCHANGE_M_FACTOR,
B.SURR_ID_ACC AS SURR_ID_ACC
FROM #ps_SourceTables.DataBase#.#ps_SourceTables.Schema#.#p_SourceTableName# A
INNER JOIN
#ps_SourceTables.DataBase#.#ps_SourceTables.Schema#.BOD_ACC_DIM B
ON
A.IDKT = B.IDKT and A.opts_acc = B.EFF_DT
WHERE
A.LoadStatusId = #p_LoadStatusId_src#

PortletPaul

do you have .sqlplan files (execution plans) for these, if so please attach them. You really will need execution plans I think.

the DDL for the tables involved will also assist

PortletPaul

oh, and when posting largish code, please used the code button in the toolbar (please?)

PortletPaul

The difference seems to be just this

	, (
		SELECT COUNT(*)
		FROM #ps_SourceTables.DataBase#.#ps_SourceTables.Schema#.#p_SourceTableName#
		WHERE LoadStatusId = #p_LoadStatusId_src#
			AND SURR_ID = a.surr_id
		) AS numb

Open in new window

but are you saying, the query WITH this runs faster then the query without it?

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Giuseppe Pizzuto

Are there INDEXES on the fields:

LoadStatusId and SURR_ID ?

They would improve performances...

Sandeep rathore

ASKER

thanks.