sqldba_newbie (4/11/2012)
Eric M Russell (4/10/2012)
Consider implementing a pass-through style query using EXEC(<sql>) AT <servername> syntax. If this query must be implemented a View, then consider replicating or grabbing a copy of the 3 remote tables to your local instance.Yeah that is another option. The issue is resolved,however my understanding is with the actual query it was copying the data locally and then doing a join instead of doing at the remote server. Is that a correct statement, i cant think of any other reason.
When you execute a SQL query that involves joins with remote tables, then it's called a distributed query. How the provider and optimizer handles this is complicated, sometimes it can offload most or all processing on the remote server or local server, or sometimes it will pull a full or partial table scan across the network to one server.
This article goes into some detail about what is going on behind the scenes.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho