How can I make this perform better?

runsqlrun 2014-04-18 16:11:47

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.

@SQLTrooper 2014-04-18 16:22:09
Are you updating statistics in the same fashion? Did you examine/compare the execution plans? They execution plan you shared isn't showing anything.
Edward Norris 2014-04-18 16:26:48
Procedure 1; Statement 10 is showing all Table Scans on large tables.
Have you tried creating indexes on them? Or, depending on the type, partitioning them out?

Ed

Dan Holmes 2014-04-18 16:26:53
ah i found it, statement10. There are some remote queries which makes this out of my knowledge area. Lots of table scans too but that is quite possibly a result of the remote query.

How many rows are really returned compared to the estimated number?

Kevin 2014-04-21 12:25:19
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.

Thanks,

-Kev