Hi,
I have a defined a complicated view vwSalesResults that returns details from tables on 3 SQL Servers.
I need to use a view rather than a stored procedure because the 3rd Party application that uses the result set does not support calling a stored procedure. It only permits you to select from a table or view.
Using SSMS
1. if I run the query
select * from vwSalesResults
857 records are returned in 8 seconds.
2. if I run the query
select * from vwSalesResults where HeadOfficeCode = 'AAA'
57 records are returned in 8.5 seconds.
3. if I run the query
select * from vwSalesResults where HeadOfficeCode = 'BBB'
12 records are returned in 84 seconds.
If I examine the actual execution plans for case 2 and case 3 they differ. 8 seconds for cases 1 and 2 is acceptable but 84 seconds for case 3 is not.
Any suggestions most gratefully received,
TIA Andy