February 7, 2012 at 12:45 pm
We recently went from SQL 2000 to SQL 2008 and about 100 of our stored procedures are running very slow compared to what they used to run on sql 2000. I am thinking a lot more people got this issue (I hope) On my INNER JOIN I am doing:
SELECT pro.Artist, m1.title
FROMtblProduct pro
INNER JOIN tblMusic m1
ON m1.PreparedName = FixArtistName(pro.Artist)
WHEREpro.PID = @PID
ANDpro.Active = 1
ANDpro.DLType IN (1, 3)
ORDER BY pro.Disc,pro.Track,pro.SongRawID,pro.SongArtistID
FixArtistName = UDF
SQL 2000 less then a second
SQL 2008 about 3 minutes to complete.
I tried doing:
exec sp_msforeachtable 'update statistics ? with fullscan'
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')
EXEC sp_updatestats
recompiling the sp's
SET ANSI_NULLS ON
and got not speed increase
Please any help would be greatly appreciated.
February 7, 2012 at 1:03 pm
Are all of your indexes recreated on the new database? Maybe a table not directly accessed through your query, but through the function?
Jared
CE - Microsoft
February 7, 2012 at 1:07 pm
All indexes match up to the old sql 2000 server. I thought the same thing about the UDF, so I created a new one just to return the same value back
UDF_Test("Foo") = returns "Foo"
February 7, 2012 at 1:43 pm
Here is something interesting....
If I take the same query and make it into a view, it comes back in a second. If I run the view query into a new window is very slow.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply