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

donb 2017-04-03 05:02:13

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.
Aaron Bertrand 2017-04-11 21:10:28
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?
donb 2017-04-12 00:12:52
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).
Aaron Bertrand 2017-04-12 12:29:46
Yeah, sometimes splitting it up is the only practical way to handle queries across servers…