April 7, 2012 at 5:02 pm
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?
April 7, 2012 at 6:20 pm
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.
April 7, 2012 at 6:37 pm
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
April 8, 2012 at 6:10 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 8, 2012 at 11:21 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.
Is that how linkedqueries work?
April 8, 2012 at 8:25 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 8, 2012 at 8:33 pm
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 🙂
April 9, 2012 at 3:48 pm
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
April 9, 2012 at 3:52 pm
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"?
April 9, 2012 at 4:04 pm
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
April 9, 2012 at 4:32 pm
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?
April 9, 2012 at 4:40 pm
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
April 9, 2012 at 4:43 pm
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.
April 9, 2012 at 4:47 pm
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
April 10, 2012 at 12:40 pm
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply