May 26, 2008 at 2:49 pm
This Store Procedure worked well in SQL Server 2000 but now is running slowly and I think timing out in SQL Server 2005. Do you have any suggestions as to why that might be happening?
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)
SelectTITLE_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
May 27, 2008 at 1:22 am
Hi,
I guess there are two things you can try.
First: Put a index on Table #tempTitleDistinct.Title_Refrnc_Nbr
Second: in your last query at
-- ValidRequestOnQueueFlag
Case when exists
(Select * from ALTA_DW..FormattedTitle FT
inner join ALTA_DW..FormattedTitleRequest FTR (nolock) on FTR.FormattedTitleSKey = FT.SKey
...
you use an implicit Cursor. Try to replace this logic. Use left/right outer join instead.
w. lengenfelder
May 27, 2008 at 1:36 am
Hello,
it is hard to tell without dissecting the code, and not knowing the data types, indexes and amount of data in tables, but I can see one possibility... there are some comparisions on character data.
What collation are you using?
SQL Server 2005 does not work well with SQL collations; you should change your collation to Windows collation. When we upgraded to 2005, many queries suddenly ran terribly slow. It took some time to find out the cause... it was collation. Once we changed that, everything was fine again.
Just look at the execution plan of some query that you think is suddenly slow. The collation problem presents itself as Table scan of all (which can be several millions) rows from a table, applying function TERTIARY_WEIGHTS on all these rows, and only then selecting those few that match criteria (in SQLS 2000, the same query used index seek in first step to select about 50 matching rows and ran a fraction of a second, on 2005 this query ran 30 seconds).
Note: I did not check, whether the procedure could be optimized in some way, since you said it worked well in 2000. However, that does not mean it can't be optimized...
May 27, 2008 at 8:55 am
w.lengenfelder (5/27/2008)
Second: in your last query at-- ValidRequestOnQueueFlag
Case when exists
(Select * from ALTA_DW..FormattedTitle FT
inner join ALTA_DW..FormattedTitleRequest FTR (nolock) on FTR.FormattedTitleSKey = FT.SKey
...
you use an implicit Cursor. Try to replace this logic. Use left/right outer join instead.
To the best of my knowledge, this is NOT an Implicit Cursor. A Cursor is a data row serialization or sequencing mechanism and this does not do that. So, although it may be slow, I do not beleive that it is an implicit cursor.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply