SQL Server 2005 - slow view performance
Posted on 2006-11-27
There seems to be a plethora of problems migrating from SLQ 2000 to 2005. However, I would like to pose this problem to see if anyone has an answer.
I have a view which runs fine in SQL 2000 and takes less than 1 sec to complete. But in 2005 it takes 3 secs. Why is this so? Here is the view:
Amount as Total,BankedAmount AS TotalBanked
FROM dbo.ClientApplications ca
INNER JOIN dbo.tblFinancialYear fy ON ca.YearID = fy.FinancialYearID
INNER JOIN dbo.ApplicationsPayments ap ON ap.AppID = ca.AppID
WHERE PaymentPurpose = 'd' and PaymentMethod = 'Cheque'
and PaymentID NOT IN
(SELECT PaymentID FROM dbo.vw_SheetApplications WHERE SheetLetter='A')
Now if you remove the NOT IN clause the result is instantaneous. And if you run the NOT IN Select clause by itself the result is instantaneous. But run the whole thing and it takes 3 secs.
The NOT IN bit returns 5000 rcds. The main bit returns about 2000 rcds.
Why does this view behave differently in 2005?