Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


remot query takes longer - local faster


remot query takes longer - local faster

Author
Message
curious_sqldba
curious_sqldba
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1543 Visits: 3573
opc.three (4/9/2012)
sqldba_newbie (4/9/2012)
opc.three (4/9/2012)
An alternative to using OPENQUERY is to use the EXECUTE...AT syntax to also force the query to execute on the remote server. OPENQUERY is a bit more flexible in that you can use it with SELECT...INTO, but the query parameter is limited to 8KB and OPENQUERY will not accept a variable.

See Example G in this article: EXECUTE

If you know the shape of the resultset you can use the INSERT...EXECUTE technique to capture rows into a local table.


This is for a view. What do you mean by "query parameter is limited to 8KB"?

OPENQUERY is only able to send 8KB of SQL to the remote server.

From the Arguments section OPENQUERY (Transact-SQL)

Arguments
linked_server
Is an identifier representing the name of the linked server.

' query '
Is the query string executed in the linked server. The maximum length of the string is 8 KB.


I have seen this limit exceeded with lengthy report queries.


thanks for detail explanation. Dumb question, how do i know size of my sql query?
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8249 Visits: 14368
sqldba_newbie (4/9/2012)
thanks for detail explanation. Dumb question, how do i know size of my sql query?

Using your sample code from earlier the bold string-literal is the 'query' argument:

SELECT  *
FROM OPENQUERY([remoteserver],
'SELECT w.ID,
w.[Rdc],
w.[Pby],
w.[Tp],
w.[Mp],
w.[OpC],
w.[PType],
w.[PCde],
w.[RTye],
w.[RCde],
CASE WHEN e.PCde IS NOT NULL THEN ''Yes''
WHEN e.pCde IS NULL THEN ''No''
END AS [Rts],
COALESCE(re.RSs, ''hold'') AS RStts
FROM (
SELECT *
FROM mydb.dbo.rdw
WHERE PType = ''T''
) w
LEFT JOIN mydb.dbo.caur e ON w.PCde = e.PCde
AND w.RCde = e.rCde
AND w.PTpe = e.PTpe
LEFT JOIN mydb.dbo.revv re ON w.Id = re.rDsId'
);




EDIT: forum cross-post edited this somehow...putting the furniture back...

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
curious_sqldba
curious_sqldba
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1543 Visits: 3573
opc.three (4/9/2012)
sqldba_newbie (4/9/2012)
thanks for detail explanation. Dumb question, how do i know size of my sql query?

Using your sample code from earlier the bold string-literal is the 'query' argument:

SELECT  *
FROM OPENQUERY([remoteserver],
'SELECT w.ID,
w.[Rdc],
w.[Pby],
w.[Tp],
w.[Mp],
w.[OpC],
w.[PType],
w.[PCde],
w.[RTye],
w.[RCde],
CASE WHEN e.PCde IS NOT NULL THEN ''Yes''
WHEN e.pCde IS NULL THEN ''No''
END AS [Rts],
COALESCE(re.RSs, ''hold'') AS RStts
FROM (
SELECT *
FROM mydb.dbo.rdw
WHERE PType = ''T''
) w
LEFT JOIN mydb.dbo.caur e ON w.PCde = e.PCde
AND w.RCde = e.rCde
AND w.PTpe = e.PTpe
LEFT JOIN mydb.dbo.revv re ON w.Id = re.rDsId'
);



Oh yeah i got this part. I was referring on how to identify size of the query, i saved the query in .sql file and the size is 2kb. I guess this would be the way to identify the size, unless there is a predefined way in sql server.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8249 Visits: 14368
SELECT LEN('SELECT  w.ID,
w.[Rdc],
w.[Pby],
w.[Tp],
w.[Mp],
w.[OpC],
w.[PType],
w.[PCde],
w.[RTye],
w.[RCde],
CASE WHEN e.PCde IS NOT NULL THEN ''Yes''
WHEN e.pCde IS NULL THEN ''No''
END AS [Rts],
COALESCE(re.RSs, ''hold'') AS RStts
FROM (
SELECT *
FROM mydb.dbo.rdw
WHERE PType = ''T''
) w
LEFT JOIN mydb.dbo.caur e ON w.PCde = e.PCde
AND w.RCde = e.rCde
AND w.PTpe = e.PTpe
LEFT JOIN mydb.dbo.revv re ON w.Id = re.rDsId');



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4623 Visits: 9571
Consider implementing a pass-through style query using EXEC(<sql>Wink 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.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
curious_sqldba
curious_sqldba
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1543 Visits: 3573
Eric M Russell (4/10/2012)
Consider implementing a pass-through style query using EXEC(<sql>Wink 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.
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4623 Visits: 9571
sqldba_newbie (4/11/2012)
Eric M Russell (4/10/2012)
Consider implementing a pass-through style query using EXEC(<sql>Wink 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.
http://blogs.msdn.com/b/conor_cunningham_msft/archive/2012/01/09/conor-vs-distributed-query-provider-hints.aspx


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
curious_sqldba
curious_sqldba
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1543 Visits: 3573
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>Wink 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.
http://blogs.msdn.com/b/conor_cunningham_msft/archive/2012/01/09/conor-vs-distributed-query-provider-hints.aspx


Thanks, very interesting. After reading this [url=http://technet.microsoft.com/en-us/library/ms178039%28SQL.90%29.aspx][/url] 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.
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4623 Visits: 9571
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>Wink 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.
http://blogs.msdn.com/b/conor_cunningham_msft/archive/2012/01/09/conor-vs-distributed-query-provider-hints.aspx


Thanks, very interesting. After reading this [url=http://technet.microsoft.com/en-us/library/ms178039%28SQL.90%29.aspx][/url] 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


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
chris.puncher
chris.puncher
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 416
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search