Remote query is taking 99% cost of local sp execution
-
So we have local sp in which remote table is udpated.this remote query part is 99% of cost of all sp (acrroding to execution plan).the sp does someting locally which i am skping as they are not factore right now ,but remote query part is.I have provide remote query and its execution plan.Accoring to execution plan its first scaning remote table to bring around 50Lakhs record then filerting lcoaly to reduce it to 25thousands rows and in last remote table is update .Kindly suggest how to tune this query so as to reduce cost or filetring to be done remotely instead locally .And all table has indexes.
why its filering locally but not remotelly ???
Belwo is query
Remotetable =RT
localtempteable =#lt
update RT
set RT.coloumnA = case when isnull(#lt.coloumnX,'')='' then 'sometinhsomething' else 'sometingelse'
from #lt inner join linkserver.remoteserver.remotedatbase with (rowlock) on #lt.coloumnB=RT.columnB
where RT.coloumnC='something'
and RT.coloumnD='something
- This topic was modified 2 days, 3 hours ago by anoop.mig29.
-
1. If RT.coloumnC etc are strings, you could try something like:
RT.coloumnC='something' COLLATE <RT.coloumnC collation>
2. If #lt is not too big, you could try creating a remote SP and passing the contents of #lt as a table variable, XML or JSON.
Personally, I would try to avoid linked servers and do the update from an application server with the application connected to both databases. This can take longer to get going if it means dusting down rusty C# skills.
-
I'm thinking along similar lines to Ken, except I'd get the data over to a staging table on the linked server and then execute a proc on the linked server to perform the update there. I'd orchestrate that process using SSIS: this avoids the need for a linked server.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Exactly THE reason why we avoid linked server usage as much as possible !
SQLserver itself determines "where" the linked server part of the query is being executed.
It may as well pull over the full object to the local instance before it even begins processing of the actual query
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
You could try forcing the processing over to the remote server through OPENQUERY so that filtering is done there. Otherwise, the way your code is written, yep, it's moving everything locally, then doing processing.
Just so you know, as several people have already said, there's no magic switch that people are hiding from you. Linked servers are notorious for being problematic exactly like this.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
openquery would not allow update of the remote table (which is what the query is doing).
linked servers should really only be used to RETRIEVE data - not to send inserts/updates - inserts for example are sent a row at the time... NO!!!!.
in many cases if a linked server table is used in joins the engine is unable (or not possible even) to send the filtering to the remote server - so ALL of the remote table is retrieved and filter done locally (as in this case). Even when its a single table its possible that the engine does not pass the filter down - and on this case using OpenQuery is the way to go as that ensures the filtering is done remotely.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply