Query runs very slow after moving to 2005

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)

Query runs very slow after moving to 2005

Author

Topic

Jessica
Starting Member

23 Posts

Posted - 2007-11-29 : 12:12:49

Please see the attached query. In SQL Server 2000, this query ran in ~1 sec. In SQL Server 2005 it takes 40 seconds!!!! Yikes. I had a look through the execution plan and in 2005 it is bringing back 100,000s of rows where in 2000 it was only find 1 row. The indexes and statistics were all rebuilt on import from 2005 to 2000. I have also run it through the DTA but and applied the suggestions but it didn't make any difference under 2005. Please help.

ALTER PROCEDURE [dbo].[spSelectCancelledTitlesByTitleNumber]
@titleNumber varchar(13),
@cancelledAfter datetime = null

AS

Declare @Level smallint
Declare @MaxLevel smallint
Declare @FurtherBreakdown smallint
Declare @SKeyCanCS as uniqueidentifier
Declare @SKeyCanTi as uniqueidentifier
Declare @SKeyCanNP as uniqueidentifier
Declare @NameCanCS as varchar(60)
Declare @NameCanTi as varchar(60)
Declare @NameCanNP as varchar(60)

Select @SKeyCanCS = SKey from ArticleType where HCKey = 'CancelledCondoPlan'
Select @SKeyCanTi = SKey from ArticleType where HCKey = 'CancelledTitle'
Select @SKeyCanNP = SKey from ArticleType where HCKey = 'CancelledNonPatent'

Select @NameCanCS = [Name] from ArticleType where HCKey = 'CancelledCondoPlan'
Select @NameCanTi = [Name] from ArticleType where HCKey = 'CancelledTitle'
Select @NameCanNP = [Name] from ArticleType where HCKey = 'CancelledNonPatent'

Set @Level = 0

If substring(@titlenumber,10,1) = '+'
and not exists (Select *
from ALTA_Staging..vwTITL TITL
inner join vwMinLTOXHTOXLINC LTOXHTOXLINC on LTOXHTOXLINC.TITLE_REFRNC_NBR = TITL.DTI_TITLE_REFRNC_NBR
where DTI_TITLE_REFRNC_NBR = @titleNumber
and DTI_TITLE_STATUS_IND in ('R','V')
and (TITL.DTI_EXPIRY_DATE is null or TITL.DTI_EXPIRY_DATE >= @cancelledAfter or @cancelledAfter is null)) begin
select @titlenumber = left(@titlenumber,9) + left('000', 13 - len(@titlenumber)) + substring(@titlenumber,11,999)
end

Select Distinct
TITL.DTI_TITLE_REFRNC_NBR as TITLE_REFRNC_NBR,
LTOXHTOXLINC.PROPRTY_PARCEL_ID,
LTOXHTOXLINC.LINC_NBR,
LTOXHTOXLINC.RIGHTS_IND,
LTOXHTOXLINC.NON_PATENT_IND,
LTOXHTOXLINC.LINCCount,
LTOXHTOXLINC.MUNC_CODE,
LTOXHTOXLINC.LTOXHTOX,
@Level as Level
into #TempTITLHist
from ALTA_Staging..vwTITL TITL
inner join vwMinLTOXHTOXLINC LTOXHTOXLINC on LTOXHTOXLINC.TITLE_REFRNC_NBR = TITL.DTI_TITLE_REFRNC_NBR
where DTI_TITLE_REFRNC_NBR = @titleNumber
and DTI_TITLE_STATUS_IND in ('R','V')
and (TITL.DTI_EXPIRY_DATE is null or TITL.DTI_EXPIRY_DATE >= @cancelledAfter or @cancelledAfter is null)

exec sp2SelectCancelledTitlesByTitleNumber @Level, @cancelledAfter

Select @MaxLevel = Max(Level) from #TempTITLHist

Select Level,
#TempTITLHist.TITLE_REFRNC_NBR as TitleNumber,
min(Case
when PROPRTY_PARCEL_ID like '%;CS' then @NameCanCS
when NON_PATENT_IND = 'N' then @NameCanTi
when NON_PATENT_IND = 'Y' then @NameCanNP
else null
end) as TitleType,
min(LINC_NBR) as LINCNumber,
min(LincCount) as LINCCount,
min(PROPRTY_PARCEL_ID) as ShortLegalDescription,
min(MUNC_CODE) as Municipality,
min(PropertyRightsType.[Name]) as RightsType,
min(TITL.DTI_CREATE_DATE) as RegistrationDate,
min(TITL.DTI_EXPIRY_DATE) as TitleChangeDate,
min(#TempTITLHist.TITLE_REFRNC_NBR) as ArticleID,
Case
when PROPRTY_PARCEL_ID like '%;CS' then @SKeyCanCS
when NON_PATENT_IND = 'N' then @SKeyCanTi
when NON_PATENT_IND = 'Y' then @SKeyCanNP
else null
end as ArticleType,
Sum(Case
when Level = @MaxLevel then
Case
when REFTITL.DTI_EXPIRY_DATE >= @cancelledAfter
or (REFTITL.DTI_EXPIRY_DATE is not null and @cancelledAfter is null) then 1
else 0
end
else null
end) as FurtherBreakdown
from #TempTitlHist
Inner join ALTA_Staging..vwTITL TITL on TITL.DTI_TITLE_REFRNC_NBR = #TempTITLHist.TITLE_REFRNC_NBR
left outer join ALTA_Staging..vwTITL REFTITL on REFTITL.DTI_TITLE_REFRNC_NBR = TITL.DRE_TITLE_REFRNC_NBR
left outer join Spin_II..PropertyRightsType PropertyRightsType on PropertyRightsType.HCKey = #TempTITLHist.RIGHTS_IND
where (LTOXHTOX = 'HTOX' or TITL.DTI_EXPIRY_DATE is not null)
and TITL.DTI_TITLE_STATUS_IND in ('R','V')
and (TITL.DTI_EXPIRY_DATE >= @cancelledAfter or @cancelledAfter is null)
Group by Level,
#TempTITLHist.TITLE_REFRNC_NBR,
Case
when PROPRTY_PARCEL_ID like '%;CS' then @SKeyCanCS
when NON_PATENT_IND = 'N' then @SKeyCanTi
when NON_PATENT_IND = 'Y' then @SKeyCanNP
else null
end
Order by Level, #TempTITLHist.TITLE_REFRNC_NBR

drop table #TempTITLHist

The above stored proc calls sp2SelectCancelledTitlesByTitleNumber. Details of that one are:
Declare @NextLevel smallint
Declare @titleNumber varchar(12)

-- This is done because it gives better performance than a join to #TempTITLHist
-- Works because there is only one title at this level or SP is not executed
Select @titleNumber = TITLE_REFRNC_NBR from #TempTITLHist where Level = @Level

Set @NextLevel = @Level + 1

Insert into #TempTITLHist
Select TITL.DRE_TITLE_REFRNC_NBR as TITLE_REFRNC_NBR ,
LTOXHTOXLINC.PROPRTY_PARCEL_ID,
LTOXHTOXLINC.LINC_NBR,
LTOXHTOXLINC.RIGHTS_IND,
LTOXHTOXLINC.NON_PATENT_IND,
LTOXHTOXLINC.LINCCount,
LTOXHTOXLINC.MUNC_CODE,
LTOXHTOXLINC.LTOXHTOX,
@NextLevel as Level
from ALTA_Staging..vwTITL TITL
inner join vwMinLTOXHTOXLINC LTOXHTOXLINC on LTOXHTOXLINC.TITLE_REFRNC_NBR = TITL.DRE_TITLE_REFRNC_NBR
where TITL.DTI_TITLE_REFRNC_NBR = @titleNumber
and DTI_TITLE_STATUS_IND in ('R','V')
and (TITL.DTI_EXPIRY_DATE is null or TITL.DTI_EXPIRY_DATE >= @cancelledAfter or @cancelledAfter is null)

If (Select count(distinct TITLE_REFRNC_NBR) from #TempTITLHist where #TempTITLHist.Level = @NextLevel) = 1 and @NextLevel <= 30 Begin
Exec sp2SelectCancelledTitlesByTitleNumber @NextLevel, @cancelledAfter
End

It seems to really bog down in this part of the SP:
Select TITL.DRE_TITLE_REFRNC_NBR as TITLE_REFRNC_NBR ,
LTOXHTOXLINC.PROPRTY_PARCEL_ID,
LTOXHTOXLINC.LINC_NBR,
LTOXHTOXLINC.RIGHTS_IND,
LTOXHTOXLINC.NON_PATENT_IND,
LTOXHTOXLINC.LINCCount,
LTOXHTOXLINC.MUNC_CODE,
LTOXHTOXLINC.LTOXHTOX,
@NextLevel as Level
from ALTA_Staging..vwTITL TITL
inner join vwMinLTOXHTOXLINC LTOXHTOXLINC on LTOXHTOXLINC.TITLE_REFRNC_NBR = TITL.DRE_TITLE_REFRNC_NBR
where TITL.DTI_TITLE_REFRNC_NBR = @titleNumber
and DTI_TITLE_STATUS_IND in ('R','V')
and (TITL.DTI_EXPIRY_DATE is null or TITL.DTI_EXPIRY_DATE >= @cancelledAfter or @cancelledAfter is null)

Please help as this is preventing our users from accomplishing certain business functionality!!! Of course.

The SQL Server 2005 instance is installed on a server that has more memory and faster processors. The disk is the same between the 2000 instance and the 2005 instance. As well, the load from users is the same.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-11-29 : 12:34:23

This sounds pretty dump thou, but does the underlying data between your 2005 and 2000 about the same?

Compared getting 1 row in 1 second in 2k vs. getting 100K rows in 40 sec in 2005, I am not sure the problem is in stat/index...

Jessica
Starting Member

23 Posts

Posted - 2007-11-29 : 12:42:22

Yes. The data is exactly the same. It was imported from 2000 to 2000 via backup and restore.

Jessica
Starting Member

23 Posts

Posted - 2007-11-29 : 12:43:54

Actually...let me be more specific on what I mean by Rows returned. The end result in both 2000 and 2005 from the query is 1 row. The difference is in the execution plans. 2000 finds one row in it's index seek and 2005 is find 100,000s of rows in it's index scan. Hopefully that is clearer.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-11-29 : 14:54:55

Then I misunderstood your original post.

Do these two views have index on the key?

ALTA_Staging..vwTITL TITL
inner join vwMinLTOXHTOXLINC LTOXHTOXLINC on LTOXHTOXLINC.TITLE_REFRNC_NBR = TITL.DRE_TITLE_REFRNC_NBR

Can you post the exec plan on the problematic part?

Jessica
Starting Member

23 Posts

Posted - 2007-11-29 : 15:07:24

vwMinLTOXHTOXLinc:
Select TITLE_REFRNC_NBR,
LINC_NBR,
RIGHTS_IND,
NON_PATENT_IND,
MUNC_CODE,
PROPRTY_PARCEL_ID,
LincCount,
'LTOX' as LTOXHTOX
from vwMinLTOXLINC

union all

Select TITLE_REFRNC_NBR,
LINC_NBR,
RIGHTS_IND,
NON_PATENT_IND,
MUNC_CODE,
PROPRTY_PARCEL_ID,
LincCount,
'HTOX' as LTOXHTOX
from vwMinHTOXLINC

vwMinHTOXLINC:
Select DHT_TITLE_REFRNC_NBR as TITLE_REFRNC_NBR,
HTXLINC.DHL_LINC_NBR as LINC_NBR,
DHL_RIGHTS_IND as RIGHTS_IND,
DPR_NON_PATENT_IND as NON_PATENT_IND,
DPR_MUNC_CODE MUNC_CODE,
DPR_PROPRTY_PARCEL_ID PROPRTY_PARCEL_ID,
(Select Count(*) from ALTA_Staging..DB_HTXLINC HTXLINCA
WHERE HTXLINCA.F_K_HTXTITL_HTXLINC = HTXLINC.F_K_HTXTITL_HTXLINC) as LincCount
from ALTA_Staging..DB_HTXLINC HTXLINC
inner join ALTA_Staging..DB_HTXTITL HTXTITL on HTXTITL.P_K = HTXLINC.F_K_HTXTITL_HTXLINC
left outer join ALTA_Staging..DB_PROPRTY PROPRTY on PROPRTY.DPR_LINC_NBR = HTXLINC.DHL_LINC_NBR
where HTXLINC.DHL_LINC_NBR = (Select Min(DHL_LINC_NBR) FROM ALTA_Staging..DB_HTXLINC HTXLINCB
WHERE HTXLINCB.F_K_HTXTITL_HTXLINC = HTXLINC.F_K_HTXTITL_HTXLINC)

vwMinLTOXLINC:
Select DXT_TITLE_REFRNC_NBR as TITLE_REFRNC_NBR,
LTXLINC.DXL_LINC_NBR as LINC_NBR,
DXL_RIGHTS_IND as RIGHTS_IND,
DPR_NON_PATENT_IND as NON_PATENT_IND,
DPR_MUNC_CODE as MUNC_CODE,
DPR_PROPRTY_PARCEL_ID as PROPRTY_PARCEL_ID,
(Select Count(*) from ALTA_Staging..DB_LTXLINC LTXLINCA
WHERE LTXLINCA.F_K_LTXTITL_LTXLINC = LTXLINC.F_K_LTXTITL_LTXLINC) as LincCount
from ALTA_Staging..DB_LTXLINC LTXLINC
inner join ALTA_Staging..DB_LTXTITL LTXTITL on LTXTITL.P_K = LTXLINC.F_K_LTXTITL_LTXLINC
left outer join ALTA_Staging..DB_PROPRTY PROPRTY on PROPRTY.DPR_LINC_NBR = LTXLINC.DXL_LINC_NBR
where LTXLINC.DXL_LINC_NBR = (Select Min(DXL_LINC_NBR) FROM ALTA_Staging..DB_LTXLINC LTXLINCB
WHERE LTXLINCB.F_K_LTXTITL_LTXLINC = LTXLINC.F_K_LTXTITL_LTXLINC)

vwTitl:
Select DTI_TITLE_REFRNC_NBR,
DTI_TITLE_STATUS_IND,
DTI_CREATE_DATE,
DTI_EXPIRY_DATE,
DRE_TITLE_REFRNC_NBR
from ALTA_Staging..DB_TITLE TITLE
left outer join ALTA_Staging..DB_REFTITL REFTITL on REFTITL.F_K_TITLE_REFTITL = TITLE.P_K

I'm not sure how to post the execution plan in a readable format on here....any ideas?

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-29 : 15:28:48

Yes, it is possible
Check the comparison table at the very end:
http://www.sqlsolutions.com/articles/articles/Temporary_Tables_vs._Table_Variables_and_Their_Effect_on_SQL_Server_Performance.htm
there are queries running about 100 times slower on 2005 then on SQL 2000

These experiments were made on SQL 2005 without any service packs,
on SQL 2005 with SP2 it is better.

I recommend you to install SP2 if it is not installed
and then play with temp tables and table veriables. Try to define primary keys for these tables.

For example, replace #TempTITLHist with @TempTITLHist

Jessica
Starting Member

23 Posts

Posted - 2007-11-29 : 15:34:24

We are running SQL SErver 2005 SP2.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources