Sorted! Your comments led me to the following website, I couldn't understand why the query was taking so long, and then timing out.

http://mssqlwiki.com/2012/10/07/optimizer-timeout-or-optimizer-memory-abort/

quote from the site below

"We can avoid optimizer from timing out and picking bad plan by enabling trace flag -T8780. This increases the time limit before the timeout occurs."

I decided to give this a shot today and guess what, that global trace flag was already on! I certainly didn't start it but my guess it is it is off when the service is restarted, and something is turning this trace flag on. I turned it off and hey presto, sql gives up finding the best plan quickly and runs the query in a couple of seconds.

I am currently running a trace to catch anything that turns this global trace flag on.

Thanks again, I would not have cracked it with your comments.