Improving the performance of this query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Improving the performance of this query

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2009-10-05 : 11:35:21

Hi, Do you see any change we could do on this to improve its performance, pls, I appreciate you taking few minutes to help with analysing and tuining it

select hoc.hoc_id,

hoc.mstr_key_id,

address.DISP_NME,

blck.st_blck_id,

blck.mstr_key_id as blck_mstr_key,

bldg.BLDG_ID,

hoc.BLDG_KEY_ID,

address.OCPD_IND,

address.TP_TYPE_CDE,

'' as cmbcNumber,

'' as tpSiteNumber,

case

when address.cmbc_id is not null then amsowner.ams_038_direct_connect.GetMobilierForCMB(address.cmbc_id)

when address.lbc_id is not null and address.cmbc_id is null then amsowner.ams_038_direct_connect.GetMobilierForLBC(address.lbc_id)

END as Mobilier,

choice.cnsmr_chce_ind,

hoc.SRT_IND,

hoc.DLVRY_IND,

case valRES_STAT_CDE

when 123444 then hoc.cse_sprtn_wdth

when 123555 then hoc_pend.cse_sprtn_wdth

when 123666 then hoc_pend.cse_sprtn_wdth

end as cse_sprtn_wdth,

case valRES_STAT_CDE

when 123444 then hoc.admail_color_id

when 123555 then hoc.admail_color_id

when 123666 then hoc_pend.admail_color_id

end as admail_color_id,

case valRES_STAT_CDE

when 123444 then hoc.tieout

when 123555 then hoc_pend.tieout

when 123666 then hoc_pend.tieout

end as tieout,

hoc.callr_Ind,

hoc.PCKUP_ind,

case

when hldout.hld_out_id > 0 then 33

else 34

end as HLDOUT_IND,

case valRES_STAT_CDE

when 123444 then hoc.BREAKER_CARD_NUM

when 123555 then hoc_pend.BREAKER_CARD_NUM

when 123666 then hoc_pend.BREAKER_CARD_NUM

end as BREAKER_CARD_IND,

Case

when valRES_STAT_CDE = 123444 and hoc.MP_ID is not null and hoc.MP_ID <> 0 then 33

when valRES_STAT_CDE = 123555 and hoc_pend.MP_ID is not null and hoc_pend.MP_ID <> 0 then 33

when valRES_STAT_CDE = 123666 and hoc_pend.MP_ID is not null and hoc_pend.MP_ID <> 0 then 33

else 34 END as MP_IND,

case

when valRES_STAT_CDE = 123444 then hoc.LCRMS_SEQ_NUM

when valRES_STAT_CDE = 123555 then hoc_pend.DEL_SEQ

when valRES_STAT_CDE = 123666 then hoc_pend.DEL_SEQ

END as DEL_SEQ,

case

when (hoc_type_cde = 154 and hoc.MP_ID is not null ) then (NVL(mp.SLCLRES_RATE , 0)+ NVL(mp.OSSDRES_RATE,0))

when (hoc_type_cde = 154 and hoc.MP_ID is null ) then (NVL(asmt.SLCLRES_RATE , 0)+ NVL(asmt.OSSDRES_RATE,0))

when (hoc_type_cde = 156 and hoc.MP_ID is not null )then (NVL(mp.SLCLCOM_RATE,0) + NVL(mp.OSSDCOM_RATE,0))

when (hoc_type_cde = 156 and hoc.MP_ID is null ) then (NVL(asmt.SLCLCOM_RATE,0) + NVL(asmt.OSSDCOM_RATE,0))

else 0

END As AvgMail,

Case

when valRES_STAT_CDE = 123444 then hoc.drct_ind

when valRES_STAT_CDE = 123555 then hoc_pend.drct_ind

when valRES_STAT_CDE = 123666 then hoc_pend.drct_ind

END as drct_ind,

Case

when pc.ldu_type_cde in (492, 564, 565, 566, 567 ) then 33

else 34

end as lvr_ind,

hoc.A12_CARD_IND,

hoc.DNC_CARD_IND,

hoc.CARD_IND,

hoc.FRCE_CARD_IND,

hoc.EXTRA_CARD_NBR,

hoc.TTL_HOC_CNT,

--(select BSNS_NME_EN from occupant where occupant.ADDR_MAIL_ID = address.addr_id and occupant.prmry_ind = 33 and rownum <= 1 )as PRIMARY_BUS_NME_EN,

--(select BSNS_NME_FR from occupant where occupant.ADDR_MAIL_ID = address.addr_id and occupant.prmry_ind = 33 and rownum <= 1 )as PRIMARY_BUS_NME_FR,

blck.blck_seq,

address.ADDR_NUM,

address.ADDR_SFX_CDE,

address.ADDR_STE_NUM,

hoc.HOC_TYPE_CDE,

hoc.CSE_SPRTN_GRP_ID,

pc.pc_id As pc_id,

pc.disp_nme as pc_disp_nme,

hoc.bag_ind,

hoc.CASETAG,

occupant.BSNS_NME_EN as PRIMARY_BUS_NME_EN , occupant.BSNS_NME_FR as PRIMARY_BUS_NME_FR

from amsowner.AMS_038_HOC hoc

left join amsowner.ams_038_hoc_pndng hoc_pend

on hoc.MSTR_KEY_ID = hoc_pend.MSTR_KEY_ID

inner join amsowner.AMS_038_ST_BlCK blck

on hoc.st_blck_key_id = blck.mstr_key_id

inner join amsowner.postal_code pc

on blck.pc_id = pc.pc_id

left join amsowner.AMS_038_bldg bldg

on hoc.BLDG_KEY_ID = bldg.MSTR_KEY_ID

inner join amsowner.address address

on address.addr_id = hoc.addr_mail_id

inner join amsowner.addr_lctn_to_mail locToMail

on locToMail.addr_mail_id = address.addr_id

inner join amsowner.addr_chce choice

on choice.addr_id = locToMail.addr_lctn_id

left join occupant on (occupant.ADDR_MAIL_ID = address.addr_id and occupant.prmry_ind = 33)

left join amsowner.ams_038_hld_out hldout

on (hldout.mstr_key_id = hoc.mstr_key_id and hldout.end_dte is null)

left join amsowner.ams_038_dm dm

on dm.dm_id = blck.dm_id

left join ams_038_assmt asmt

on blck.pc_id = asmt.pc_id and asmt.dpt_cde_nme = valDpt_cde_nme and ((asmt.case_type_cde = 1220 and dm.a62_cse_ind = 33) or (asmt.case_type_cde = 1219 and dm.a62_cse_ind = 34))

left join ams_038_mail_prfl mp

on hoc.mp_id = mp.mp_id and ((mp.case_type_cde = 1220 and dm.a62_cse_ind = 33) or (mp.case_type_cde = 1219 and dm.a62_cse_ind = 34))

where hoc.mstr_key_id = valMSTR_KEY_ID and blck.DPT_CDE_NME = valDpt_cde_nme and hoc.DPT_CDE_NME = valDpt_cde_nme and blck.RSTRCTR_STAT_CDE = restCode;

End GetHOCInfoByHOCMasterKey;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-05 : 13:57:50

I dont anybody would take the pain of analysing the above code. could you analyse the above query using execution plan and identify costly steps?

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-05 : 14:03:57

what's the performance now?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-05 : 14:14:31

formatting the code might help to read it better....

Do all of the joins have a Primary Key or an associated index?

Do you have an index for the predicates ion the WHERE Clause?

The SELECT will have nothing (or very little) to do with performance


      FROM amsowner.AMS_038_HOC						hoc
 LEFT JOIN amsowner.ams_038_hoc_pndng				hoc_pend
		ON hoc.MSTR_KEY_ID = hoc_pend.MSTR_KEY_ID
INNER JOIN amsowner.AMS_038_ST_BlCK					blck
		ON hoc.st_blck_key_id = blck.mstr_key_id
INNER JOIN amsowner.postal_code						pc
		ON blck.pc_id = pc.pc_id
 LEFT JOIN amsowner.AMS_038_bldg					bldg
		ON hoc.BLDG_KEY_ID = bldg.MSTR_KEY_ID
INNER JOIN amsowner.address							address
		ON address.addr_id = hoc.addr_mail_id
INNER JOIN amsowner.addr_lctn_to_mail				locToMail
		ON locToMail.addr_mail_id = address.addr_id
INNER JOIN amsowner.addr_chce						choice
		ON choice.addr_id = locToMail.addr_lctn_id
 LEFT JOIN occupant 
		ON (occupant.ADDR_MAIL_ID = address.addr_id 
	   AND occupant.prmry_ind = 33)
 LEFT JOIN amsowner.ams_038_hld_out					hldout
		ON (hldout.mstr_key_id = hoc.mstr_key_id 
	   AND hldout.end_dte is null)
 LEFT JOIN amsowner.ams_038_dm						dm
		ON dm.dm_id = blck.dm_id
 LEFT JOIN ams_038_assmt							asmt
		ON blck.pc_id = asmt.pc_id 
	   AND asmt.dpt_cde_nme = valDpt_cde_nme 
	   AND (	(asmt.case_type_cde = 1220 AND dm.a62_cse_ind = 33) 
			OR  (asmt.case_type_cde = 1219 AND dm.a62_cse_ind = 34))
 LEFT JOIN ams_038_mail_prfl						mp
		ON hoc.mp_id = mp.mp_id 
	   AND (	(mp.case_type_cde = 1220 AND dm.a62_cse_ind = 33) 
			OR  (mp.case_type_cde = 1219 AND dm.a62_cse_ind = 34))
     WHERE hoc.mstr_key_id       = valMSTR_KEY_ID 
	   AND blck.DPT_CDE_NME      = valDpt_cde_nme 
	   AND hoc.DPT_CDE_NME       = valDpt_cde_nme 
	   AND blck.RSTRCTR_STAT_CDE = restCode;

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2009-10-05 : 16:25:11

In addition to what everyone else has said, you have a problem here:

case
when address.cmbc_id is not null then amsowner.ams_038_direct_connect.GetMobilierForCMB(address.cmbc_id)
when address.lbc_id is not null and address.cmbc_id is null then amsowner.ams_038_direct_connect.GetMobilierForLBC(address.lbc_id)
END as Mobilier,

You will end up calling one of the above functions for every row selected in the result set. That can be very costly and slow the query down tremendously. I would look at the functions called in the above and see if you can improve the performance of those functions.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2009-10-05 : 16:26:09

Oh, one more thing - is the MySQL or MSSQL? I am not aware of an inbuilt NVL function in MSSQL.

rtutus
Aged Yak Warrior

522 Posts

Posted - 2009-10-06 : 08:43:28

waw thanks a lot, that Pl-SQL :) :) I figured it d be the same thing
again i appreciate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources