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