Linked server: Distributed query with WHERE CLAUSE AND INNER JOIN is slow

Using an OPENQUERY is not an option. Trying to use a distributed query with NOLOCK HINT and getting a MAX(SALE_AMOUNT) from remote server table containing 500 million rows and using an inner join to match a local table REPNO with remote table REPNO value. This query takes about average 4 minutes to complete. Looking to optimize this query and appreciate any suggestions.

avatar image By donb 1 asked Apr 03 at 05:02 AM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Apr 11 at 09:10 PM

Are you able to manipulate the table at the other end (say, by adding an index better suited for your query, or pre-aggregating / denormalizing the aggregates you're after)? How about remote join hints? Why is OPENQUERY not an option?

10|10000 characters needed characters left

1 answer: sort voted first

Thank you very much for your response Aaron! I was under the impression that only NOLOCK hints work on remote queries. The execution plan does show it is using proper index seek. I am working for a financial bank and it appears the security team is using a tool to block remote queries and it times out to prevent SQL injection. At least that is what I was told. I saw the configuration parameter 'ad hoc distributed query' was disabled. I was trying to use OPENQUERY and that is howI figured out that it was not an option for me to optimize the query. So, my next alternative was to use distributed query to bring the required data without doing join and aggregation and pump it to a temp table locally and then do the aggregation and join with local tables and was able to improve on timings. (average 2minutes).

avatar image By donb 1 answered Apr 12 at 12:12 AM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Apr 12 at 12:29 PM

Yeah, sometimes splitting it up is the only practical way to handle queries across servers...

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



asked: Apr 03 at 05:02 AM

Seen: 20 times

Last Updated: Apr 12 at 12:29 PM