I have a query that calls a remote table through Linked Server. However, in the execution plan the Remote Query is 71% of total cost and the time taken to run is over 4mins. It returns all rows in the remote table (Actual Number of Rows value is over 1 million).
If I run the remote query separately, the Actual Number of Rows value is 2.
Why does it load all rows?
Below is the subquery part that calls the remote tables...
If I run the remote query part (the subquery) in a new window, the Actual Number of Rows is 2, and time taken is 1 second...
OPENQUERY()
? You can push theDISTINCT
,JOIN
andWHERE
filtering to the remote server, instead of downloading all the table rows and filtering them out on your local server.