Yes, very fair question. (and I can understand this question particularly as a reactionary question, however if you stop and think about it, I think if I were running it on the 'remote' server, let's call it Server1, and complaining about the slowness of the 90% of the query that runs on the other server Server2, isn't there a great chance you'd ask...Why aren't you running this on Server2? I mean why either one vs. the other, right? There's data pulls from both).
Well when I have queries that involve code on several or at least 2 servers, and there is a link, and I have access to both so there is a choice, a number of factors go into my decision or at least my impulse on where I want to code it:
- Where the 'majority' of the code/processing is occurring. Most of the heavy lifting, or so I thought, is occurring on Server2, hence, I figured OK let's do this using a connection to Server2, and we'll push the insert to Server1.
- Where I am less familiar with the tables, and can really use the help of intellisense, so I was developing on Server2 to help me out. Of course I can change it now, but, I was so shocked at this slowness that I thought I might ask for input.
- In this particular case, I had experienced annoying slowness when pulling data (from being connected to Server1, pulling from Server2) so in this case I thought I'd avoid that by treating Server1 as remote.
Guess I'm just surprised that an Insert, with data already provisioned and ready to be plopped somewhere, theoretically it seems very counter intuitive that it would be so slow ....
Oh just saw your link and read it over. Yeah, I guess the blanket answer probably deserves to just go back to the linked server and how i shouldn't expect much of anything from them (although I'm really skeptical of someone who just gives that blanket answer all the time as opposed to accepting that there may actually be some differences between scenarios worth studying for those of us who can't just "... I replace them wherever I see them". (with what? magic?)
I mean in this case I can use SSIS as a fall back if absolutely necessary, but if this were a user-requested report I couldn't.
Uhhh. Too many servers.