Yusuf Ali Bhiwandiwala (10/11/2012)
Thanks Chandan and Chris,Have already tried the OPENQUERY and the recent EXECUTE AT remoteserver feature (latter having the much needed ability to pass parameters) and both work wonderfully well for this query.
The thing that amazes me is that this performance problem is not consistent. At times, it goes through and intermittently it does not.
I am unable to explain to my users why a thing that was working in the past (4 part query with the variable) does not work now, and why the code change is required.
That could be due to network at times. You can consider replication if the things make too much of noise to have an identical copy at your end to be queried. With linked server you cannot do much at times.
But 1 thing you are doing rightly is to look what query is being executed at remote end if it has filters because often the query executed at linked server end does not carry filters with them causing a complete scan. You may also test importing the data locally in temp tables and then doing joins on them if required. So it all boils down to what you want vs what you can configure and how far!!!
Thanks
Chandan