Slow Running SP - SQL Server Forums

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)

Slow Running SP

Author

Topic

Jessica
Starting Member

23 Posts

Posted - 2008-05-26 : 16:46:41

I think this SP is causing me grief in SQL Server 2005 when it would run relatively well in SQL Server 2000. Can anyone provide any suggestions:

ALTER PROCEDURE [dbo].[sp2SelectTitleTextForPendingOrder]
@PendingOrderSKey uniqueidentifier

as

Declare @SKeyCurCS as uniqueidentifier
Declare @SKeyCurTi as uniqueidentifier
Declare @SKeyCurNP as uniqueidentifier

Select @SKeyCurCS = SKey from ArticleType where HCKey = 'CurrentCondoPlan'
Select @SKeyCurTi = SKey from ArticleType where HCKey = 'CurrentTitle'
Select @SKeyCurNP = SKey from ArticleType where HCKey = 'CurrentNonPatent'

Declare @PendingOrderChangedDate datetime
Declare @PendingOrderProductSourceHCKey varchar(30)

Set @PendingOrderChangedDate = (Select ChangedDate from PendingOrder where Skey = @PendingOrderSKey)
Set @PendingOrderProductSourceHCKey = (Select ProductSource.HCKey
from PendingOrder
INNER JOIN ProductSource on ProductSource.SKey = PendingOrder.ProductSourceSKey
where PendingOrder.Skey = @PendingOrderSKey)

-- Create a 2nd temp table of unique title numbers and add the LastRegistrationDate (because of the outer join it's inadvisable to do the date seelction here)
Select TITLE_REFRNC_NBR,
min(NON_PATENT_IND) as NON_PATENT_IND ,
min(PROPRTY_PARCEL_ID) as PROPRTY_PARCEL_ID,
min(LastRegistrationDate) as LastRegistrationDate
into #tempTitleDistinct
from #tempTitleList
left outer join ALTA_Staging..vwLastDocumentRegistrationDateForLiveTitle LRCD on LRCD.TitleReferenceNumber = #tempTitleList.TITLE_REFRNC_NBR
group by TITLE_REFRNC_NBR
order by TITLE_REFRNC_NBR

If Not @PendingOrderChangedDate is null begin

-- If a change date exists, prefilter the list (not done on load due to min check on LastRegistrationDate)

delete from #tempTitleDistinct
where LastRegistrationDate < @PendingOrderChangedDate

end

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- Return the results
-- Clean titles
Select
@PendingOrderSKey as PendingOrderSKey,
TD.Title_Refrnc_Nbr as ArticleID,
Case
when PROPRTY_PARCEL_ID like '%;CS' then @SKeyCurCS
when NON_PATENT_IND = 'N' then @SKeyCurTi
when NON_PATENT_IND = 'Y' then @SKeyCurNP
else null
end as ArticleType,
FT.[Text],
1 as CleanTitleIndicator,
0 as ValidRequestOnQueueFlag
from #tempTitleDistinct TD
inner join ALTA_DW..FormattedTitle FT on FT.TitleReferenceNumber = TD.Title_Refrnc_Nbr
inner join ALTA_DW..FormattedTitleType FTT on FTT.SKey = FT.FormattedTitleTypeSKey
inner join ALTA_Staging..TitleDataReplicationDateTime TDR on TDR.TitleReferenceNumber = FT.TitleReferenceNumber
where (LastRegistrationDate >= @PendingOrderChangedDate or @PendingOrderChangedDate is null) -- keep date check for performance
and FT.[Text] is not null
and FT.LastReceivedTextRequestedDateTime > TDR.TitleDataReceiveDateTime
and FTT.HCKey = 'Current' + @PendingOrderProductSourceHCKey

union all

-- Dirty Titles
Select
@PendingOrderSKey as PendingOrderSKey,
TD.Title_Refrnc_Nbr as ArticleID,
Case
when PROPRTY_PARCEL_ID like '%;CS' then @SKeyCurCS
when NON_PATENT_IND = 'N' then @SKeyCurTi
when NON_PATENT_IND = 'Y' then @SKeyCurNP
else null
end as ArticleType,
null,
0 as CleanTitleIndicator,
-- ValidRequestOnQueueFlag
Case when exists
(Select * from ALTA_DW..FormattedTitle FT
inner join ALTA_DW..FormattedTitleRequest FTR (nolock) on FTR.FormattedTitleSKey = FT.SKey
where
FT.TitleReferenceNumber = TD.Title_Refrnc_Nbr
and FTR.ReceiveDateTime is null
and FTR.FailureDateTime is null
and DateDiff (minute, FTR.RequestDateTime, getdate()) < 30
) --30 minutes
then 1
else 0
end as ValidRequestOnQueueFlag
from #tempTitleDistinct TD
where (LastRegistrationDate >= @PendingOrderChangedDate or @PendingOrderChangedDate is null) -- keep date check for performance
and not exists (Select * from ALTA_DW..FormattedTitle FT
inner join ALTA_DW..FormattedTitleType FTT on FTT.SKey = FT.FormattedTitleTypeSKey
inner join ALTA_Staging..TitleDataReplicationDateTime TDR on TDR.TitleReferenceNumber = FT.TitleReferenceNumbeR
where FT.TitleReferenceNumber = TD.Title_Refrnc_Nbr
and FT.[Text] is not null
and FT.LastReceivedTextRequestedDateTime > TDR.TitleDataReceiveDateTime
and FTT.HCKey = 'Current' + @PendingOrderProductSourceHCKey)
--FOR XML AUTO, XMLDATA

Drop table #tempTitleList
Drop table #tempTitleDistinct

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources