April 11, 2012 at 4:47 pm
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.
April 12, 2012 at 7:20 am
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
April 12, 2012 at 9:10 am
Eric M Russell (4/12/2012)
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.
Thanks, very interesting. After reading this looks like i could use hints and offload the join on remote server. But where do i embed these query hints? I did a parse on the code and gave error.
April 12, 2012 at 10:04 am
sqldba_newbie (4/12/2012)
Eric M Russell (4/12/2012)
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.
Thanks, very interesting. After reading this looks like i could use hints and offload the join on remote server. But where do i embed these query hints? I did a parse on the code and gave error.
I bookmarked that article a long time ago, but never really read it, because I perform all of my remote queries using the EXEC() AT syntax I mentioned earlier. Looking at the article, it seems that these hints are actually implmented when programming provider libraries, they are not really T-SQL hints. Distribution of joins are handled by the OLEDB database provider and SQL Server optimizer.
There is a REMOTE clause that can be added to a JOIN, but from what I read in MSDN, it was necessary only for SQL Server 2000 and earlier versions.
Here is what Books Online for SQL Server 2008 says about the REMOTE JOIN hint:
Because SQL Server considers distribution and cardinality statistics from linked servers that provide column distribution statistics, the REMOTE join hint is not required to force evaluating a join remotely. The SQL Server query processor considers remote statistics and determines whether a remote-join strategy is appropriate. REMOTE join hint is useful for providers that do not provide column distribution statistics. For more information, see Distribution Statistics Requirements for OLE DB Providers.
http://technet.microsoft.com/en-us/library/ms177634(v=sql.100).aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 5, 2013 at 8:54 am
Grant Fritchey (4/8/2012)
That's going to bring all of the remote server's data back and then perform filtering on it. Horribly inefficient. Instead, look to OPENQUERY where you can pass some parameters that will filter the data on the other server before transferring it over.
Hey Grant,
Just got your free "SQL Server Execution Plans" ebook this morning - great read.
When you say all the data is going to come back, is that based on it being a distributed query, or because of the use of LEFT JOINs? The reason I ask is that I am trying to improve the performance of a distributed query. In my execution plan the expensive Remote Query node has an Actual Number of Rows of 1 (remote view has about a million rows) and the Remote Query SQL contains the parameterized WHERE clause, so to me that sounds like the filtering is occurring on the linked server. Also using OPENQUERY is no faster.
I'm currently trying to trawl through the Google mud and separate the good advice from the urban myths, so any wise words greatly received.
Cheers,
Chris
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply