In development, this query is taking around 10 minutes to execute. But in QA, it has been running over an hour and hasn't stopped yet.
By runsqlrun 1 asked Apr 18, 2014 at 04:11 PM
It looks like you're using standard linked server queries. Under this approach, SQL Server basically performs a SELECT * FROM linked.server.source, and gives terrible performance. After it retrieves the entire result set, it then performs your sorts, joins, etc.
You're usually much better off using the OPENQUERY pattern (http://msdn.microsoft.com/en-us/library/ms188427(v=sql.110).aspx). That way the work of reducing the result set is pushed over to the linked server rather than to the calling server.
Give it a try and let us know what the execution plan looks like after refactoring.
By Kevin 141 answered Apr 21, 2014 at 12:25 PM