How can I make this perform better?

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.

avatar image By runsqlrun 1 asked Apr 18, 2014 at 04:11 PM
more ▼
(comments are locked)
avatar image @SQLTrooper Apr 18, 2014 at 04:22 PM

Are you updating statistics in the same fashion? Did you examine/compare the execution plans? They execution plan you shared isn't showing anything.

avatar image Edward Norris Apr 18, 2014 at 04:26 PM

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

avatar image Dan Holmes Apr 18, 2014 at 04:26 PM

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?

10|10000 characters needed characters left

1 answer: sort voted first

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

avatar image By Kevin 141 answered Apr 21, 2014 at 12:25 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x455
x109
x89
x16

asked: Apr 18, 2014 at 04:11 PM

Seen: 237 times

Last Updated: Apr 21, 2014 at 12:25 PM