How can I make this perform better?
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.
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?
Have you tried creating indexes on them? Or, depending on the type, partitioning them out?
Ed
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?
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