Here's a WAG first reaction on my part : I would be curious to know more about the HeadOffideCode colum - data type and contents. (and of course the exact indexes) For example, is there only AAA but might be BBB, BB1, BBB2.

Would this make a difference?

And what would be the difference if he used Where HeadofficeCode like 'BBB%'

All of those are possible (and yes, data type definitely significant)

I suspect there might not be any indexes (or any relevant ones) - the execution plan had table scans in it. Also, it looks like at least part of the view was using a linked server, and so the issue might be in something on another server (and I meant to ask him if the linked server was in SQL or Oracle and what that query might be doing)