Temporary Table Insert Performance Degraded After Migration To 2005 By 50%.

Hi,



I have a series of queries which have doubled in the amount of time they take to execute since moving to SQL Server 2005. The queries being performance tested are utilising hardware that is very similar to that of the comparison SQL Server 2000 server. They have 6 CPUs exactly the same and we have swapped RAM around to eliminate that difference.



There are 4 parts to the query suffering performance degredation.



1. Create temporary results table

2. Create (using SELECT INTO) and populate temporary working table 1 - 212,263 rows

3. Create (using SELECT INTO) and populate temporary working table 2 - 5,102 rows

4. Insert into temp results table matches found in temp work table 1 and temp work table 2 - 382 rows



On 2000, the queries take a total of 15 secs. On 2005, they take 30 seconds. Part four of the query takes approx 17 secs on its initial run. However, if i truncate the temp results table and re-run just the last query it only takes 1 sec. Query Plan caching?



I have reviewed the forum for a solution to the problem but with no luck. Many of the solutions presented appear to relate to permanant user tables. Solutions such as UPDATE STATISTICS and recompiling stored procedures have no positive effect. When reviewing the query plans, there are very little differences. Some expected between versions right?



The following code snippet is the query from part 4.




Code Snippet

INSERT #MatchingResults

(Table1IDNo, Table2IDNo, MatchRunNo)

SELECT DISTINCT #Table2.IDNo AS Table2IDNo,

#Table1.IDNo AS Table1IDNo,

1 AS MatchRunNo

FROM #Table1

INNER JOIN #Table2

ON ( #Table2.LastName = #Table1.LastName )

AND ( #Table2.AddressDetails = #Table1.AddressDetails )

AND ( #Table2.Country = #Table1.Country )

AND ( ( #Table2.FirstName = #Table1.FirstName) OR ( #Table1.FirstName = '' ) )

AND ( ( #Table2.Title = #Table1.Title ) OR ( #Table1.Title = '' ) )



The query plan shows a hash join on both servers. I have tried removing the distinct statement and forcing a Loop Join (query hint).



I have also run SQL Profiler. The only differences there appear to be with the "SELECT StatMan" statements.



On 2000, part of the query duration is 1719 and is as follows:


Code SnippetSELECT statman([AddressDetails],[LastName],[FirstName],[Title],[Country],@PSTATMAN)
FROM (SELECT TOP 100 PERCENT [AddressDetails],[LastName],[FirstName],[Title],[Country] FROM [dbo].[#TMCT04042007101009_________________________________________________________________________________________________000000000096] WITH(READUNCOMMITTED,SAMPLE 3.675520e+001 PERCENT) ORDER BY [AddressDetails],[LastName],[FirstName],[Title],[Country]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1)



On 2005, part of the query duration is 5188 and is as follows:


Code Snippet

SELECT StatMan([SC0], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000] FROM (SELECT [AddressDetails] AS [SC0] FROM [dbo].[#TMCT04042007101009_________________________________________________________________________________________________00000000000E] WITH (READUNCOMMITTED,SAMPLE 7.946877e+001 PERCENT) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)



Its clear that the sampling rate is higher. I assume this could have something to do with it. Can this be modified?



Thank-you for your help in advance..



Cheers



Tim