This monstrous query is very hard to read and impossible to understand. Can you break it to managable pieces using either CTE or temp tables?


>Hi All, I've been asked to look at speeding up the following SP - my TSql is very limited. I ran Show Execution Plan and it indicated that the query's on the LetterHistory table accounted for 68% of Query cost - I have no idea as to how I can achieve the results any other way - here is the query in all it's glory
>
>

>ALTER PROCEDURE [dbo].[GetClaimDetailsByMasterRefDev](@MasterRef char(10)) with recompile
>AS
>set dateformat dmy
>Declare @POALetterCode int
>Declare @PIFLetterCode int
>Select @PIFLetterCode = dbo.GetLetterCode('PIF',@MasterRef) -- 20
>Select @POALetterCode = dbo.GetLetterCode('POA',@MasterRef) -- 21
>SELECT	
>dbo.Claim.ClaimRef,
>dbo.Claim.MasterRef,
>ClaimStatusDesc = CASE dbo.Claim.ClaimStatus
>	When '01' Then 'Inc' 
>	When '02' Then 'Chk'
>	When '03' Then 'Agr'
>	When '04' Then 'Rej'
>	When '05' Then 'Adm'
>	Else '' end,
>dbo.Claim.ClaimStatus,
>dbo.Claim.CreditorName,
>dbo.Claim.ClaimNarrative,
>dbo.Claim.PayDetailsID,
>dbo.Claim.AdmittedValUSD,
>convert(varchar(10),dbo.ListNos.listdate,5) As AdmittedDate,
>dbo.PaymentDetails.PayAddressID,
>dbo.PaymentDetails.PIFStatusCode,
>(CASE WHEN ((dbo.Claim.PayDetailsConf = 0)
>	OR  
>	EXISTS (Select PayDetailsID From TTDetails Where PayDetailsID = Claim.PayDetailsID
>	And TTDetailsConf = 0)) And ClaimStatus = '05' THEN 'Y'
>	WHEN ClaimStatus != '05' THEN ''
>	ELSE 'N' END)
>As PIF,
>dbo.Master.CorrAddressID,
>convert(varchar(10),dbo.Claim.PayDetailsConfDate,5) As PayDetailsConfDate,
>dbo.Claim.PayDetailsConfUser,
>dbo.Claim.PayDetailsConf,
>PayDetailsRequestedDate = (Select convert(varchar(10),Max(LetterSentDate),5)
>From dbo.LetterHistory Where claimref = claim.claimref and lettercode = @PIFLetterCode),
>PayDetailsRequestedUser = (select top 1 LetterSentUser from letterhistory
>where LetterSentDate = (select max(LetterSentDate) from LetterHistory 
>Where LetterCode = @PIFLetterCode And ClaimRef = Claim.ClaimRef) And ClaimRef = Claim.ClaimRef),
>(Select Distinct convert(varchar(10),Max(DateReceived),5)
>From dbo.PayDetailsReplies
>Where ClaimRef = Claim.ClaimRef) As PayDetailsReceiveddate,
> (Select Top 1 InputBy 
> from PayDetailsReplies
> where 
> DateReceived = (select max(DateReceived) 
> from PayDetailsReplies 
> Where ClaimRef = Claim.ClaimRef)) As PayDetailsReceivedUser,
>dbo.Address.CountryCode,
>dbo.Listnos.luxoruk,
>dbo.Claim.AdmittedListNo,
>dbo.multiblk.block As PayBlockCode,
>PayBlockDescription = 
>multiblk.block + ': - ' + rtrim(blockpay.blkd_desc) + ' - '
>+ multiblk.raised_by + ' - ' 
>+ convert(varchar(10),multiblk.raised_dt,5),
>(select count(block)
>from dbo.multiblk
>Where active = 1
>and clm_ref = dbo.Claim.ClaimRef) As NumBlocks,
>(CASE WHEN EXISTS
>(SELECT block
>FROM dbo.multiblk
>WHERE dbo.multiblk.block = '40' 
>AND 
>dbo.Multiblk.clm_ref = dbo.Claim.ClaimRef 
>AND 
>dbo.Multiblk.Active = 1) 
>THEN 'Y' ELSE 'N' END) 
>AS GadFlag,
>(CASE WHEN EXISTS
>(SELECT block
>FROM dbo.multiblk
>WHERE dbo.multiblk.block = '94' 
>AND 
>dbo.Multiblk.clm_ref = dbo.Claim.ClaimRef 
>AND 
>dbo.Multiblk.Active = 1) 
>THEN 'Y' ELSE 'N' END) 
>AS Deceased,
>Art39.BenstatusCode As DBO,
>(Select distinct(convert(varchar(10),Max(LetterSentDate),5)) 
>from LetterHistory
>Where LetterCode =@POALetterCode
>And ClaimRef = Claim.ClaimRef) As POADetailsRequestedDate,
>(select distinct(LetterSentUser)
>from letterhistory
>where 
>LetterSentDate = (select distinct(Max(LetterSentDate))
>from LetterHistory 
>Where LetterCode = @POALetterCode)
>And ClaimRef = Claim.ClaimRef) As POADetailsRequestedUser,
>(Select convert(varchar(10),Max(DateReceived),5)  
>from POADetailsReceived
>Where ClaimRef = Claim.ClaimRef) As POADetailsReceivedDate,
>(select distinct(InputBy) 
>from POADetailsReceived
>where 
>DateReceived = (select Max(DateReceived) 
>from POADetailsReceived 
>Where ClaimRef = Claim.ClaimRef)) As POADetailsReceivedUser,
>DBOReceivedDate = (Select convert(varchar(10),Max(DBOReceivedDate),5) From dbo.DBOSReceived
>Where dbo.DBOSReceived.ClaimRef = Claim.ClaimRef),
>DBOReceivedUser = (Select DBOReceivedUser From dbo.DBOSReceived
>Where dbo.DBOSReceived.ClaimRef = Claim.ClaimRef And DBOReceivedDate
>= (Select Max(DBOReceivedDate) From dbo.DBOSReceived
>Where dbo.DBOSReceived.ClaimRef = Claim.ClaimRef)),
>lds.due,
>lds.dpbflag,
>lds.adjustflag,
>lds.empdivflag
>From dbo.Claim 
>Left Outer Join 
>	Listnos
>	ON Claim.AdmittedListNo = Listnos.AdmitList
>Left Outer Join 
>	Art39
>	ON Claim.ClaimRef = Art39.ClaimRef
>Inner JOIN
>	dbo.Master 
>	On  dbo.Master.MasterRef = dbo.Claim.MasterRef
>Inner Join 
>	dbo.Address
>	On Master.CorrAddressID = dbo.Address.AddressID
>Inner Join dbo.PaymentDetails
>	On PaymentDetails.PayDetailsID = dbo.Claim.PayDetailsID
>left outer join 
>	lds.dbo.vw_cms_claims_main lds
>	on dbo.Claim.ClaimRef = lds.claim_reference
>left outer join multiblk
>on Claim.ClaimRef = multiblk.clm_ref And multiblk.Status = 1 And multiblk.Active = 1
>left outer join blockpay
>On blockpay.blkd_code = multiblk.block 
>Where dbo.Claim.MasterRef = @MasterRef
>Order By Claim.ClaimRef
>