Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

remot query takes longer - local faster Expand / Collapse
Author
Message
Posted Monday, April 9, 2012 4:32 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:01 AM
Points: 1,299, Visits: 3,003
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?
Post #1280496
Posted Monday, April 9, 2012 4:40 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:51 PM
Points: 7,140, Visits: 12,763
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
Post #1280503
Posted Monday, April 9, 2012 4:43 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:01 AM
Points: 1,299, Visits: 3,003
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.
Post #1280506
Posted Monday, April 9, 2012 4:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:51 PM
Points: 7,140, Visits: 12,763
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
Post #1280508
Posted Tuesday, April 10, 2012 12:40 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:21 AM
Points: 1,793, Visits: 5,044
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.
Post #1281046
Posted Wednesday, April 11, 2012 4:47 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:01 AM
Points: 1,299, Visits: 3,003
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.
Post #1282054
Posted Thursday, April 12, 2012 7:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:21 AM
Points: 1,793, Visits: 5,044
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.
http://blogs.msdn.com/b/conor_cunningham_msft/archive/2012/01/09/conor-vs-distributed-query-provider-hints.aspx
Post #1282351
Posted Thursday, April 12, 2012 9:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:01 AM
Points: 1,299, Visits: 3,003
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.
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.
Post #1282474
Posted Thursday, April 12, 2012 10:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:21 AM
Points: 1,793, Visits: 5,044
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.
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
Post #1282542
Posted Friday, April 5, 2013 8:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:00 AM
Points: 29, Visits: 367
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
Post #1439265
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse