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 Saturday, April 7, 2012 5:02 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 9:31 AM
Points: 1,294, Visits: 2,991

I have a query which does a join between remote table and a local time. I know this design is terrible, i am looking if there is any way i can improve it. When the same table is local it is doing a perfect seek and is much faster. For now is there a way to force seek on remote table or changing the design is ONLY option?
Post #1279934
Posted Saturday, April 7, 2012 6:20 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:42 AM
Points: 20,799, Visits: 32,717
Sounds like you are using a linked server. If you provide the query you are currently using we may be able to show you how to improve its performance. Without seeing it, there really isn't much we can do.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1279937
Posted Saturday, April 7, 2012 6:37 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 9:31 AM
Points: 1,294, Visits: 2,991
Lynn Pettis (4/7/2012)
Sounds like you are using a linked server. If you provide the query you are currently using we may be able to show you how to improve its performance. Without seeing it, there really isn't much we can do.


Actually i take it back , issue isn't around the join across servers but it is on join which happens on remote server and sends back the data. This is inside a view:


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 [remoteserver].mydb.dbo.rdw WHERE PType = 'T' ) w
LEFT JOIN [remoteserver].mydb.dbo.caur e
ON w.PCde = e.PCde
AND w.RCde = e.rCde
AND w.PTpe = e.PTpe
LEFT JOIN [remoteserver].mydb.dbo.revv re
ON w.Id = re.rDsId


So above query is executed from serverA against remoteserver and sends back the result to serverA. I think the issue is around first left join. If i ran the same query on the remoteserver runs perfectly fine. Thanks
Post #1279940
Posted Sunday, April 8, 2012 6:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:38 PM
Points: 13,999, Visits: 28,378
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.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1279967
Posted Sunday, April 8, 2012 11:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 9:31 AM
Points: 1,294, Visits: 2,991
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.


Is that how linkedqueries work?
Post #1279986
Posted Sunday, April 8, 2012 8:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:38 PM
Points: 13,999, Visits: 28,378
Similar in concept, but OPENQUERY is like a pass through query. It puts the work on to the remote server.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1280025
Posted Sunday, April 8, 2012 8:33 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 9:31 AM
Points: 1,294, Visits: 2,991
Grant Fritchey (4/8/2012)
Similar in concept, but OPENQUERY is like a pass through query. It puts the work on to the remote server.



this really worked. I replaced the entire query and put in the form of OPENQUERY. The view which use to take 40 secs came down to 16 secs !!!...Thank you so much. Learned something new again :)
Post #1280026
Posted Monday, April 9, 2012 3:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:02 AM
Points: 7,135, Visits: 12,744
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.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1280481
Posted Monday, April 9, 2012 3:52 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 9:31 AM
Points: 1,294, Visits: 2,991
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"?
Post #1280483
Posted Monday, April 9, 2012 4:04 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:02 AM
Points: 7,135, Visits: 12,744
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.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1280485
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse