sql-lover (5/3/2015)
TheSQLGuru (5/1/2015)
sql-lover (5/1/2015)
TheSQLGuru (5/1/2015)
I didn't read deeply (TL;DR) :), but the first thing I would do is put the query into a sproc on the remote side and call that from local box.Thanks for reply.
Maybe I am not following you, but the query is already inside an sproc. The sproc is called on Server2, then is executed, connect to Server1 (where the table resides) and give the result back. Are you saying that the select statement should reside on Server1? If that's the case, that's another no, no. This is actually a major, major code redesign and simply is not possible.
The code should reside, and execute, on the server where the table is. Can't see why it is a "major, major code redesign" to simply call a sproc that inside itself calls a sproc on a different server. That isn't a change to the calling mechanism at all. But if you can't do it for some reason then you can't.
Thousands of store procedures to change, lot of dependencies. It is a major change. I know the app, you don't.
I don't care about your app or it's thousands of sprocs, nor do I need to know a thing about it other than it calls a sproc that does a remote server SELECT. I was addressing your singular request to address performance in that single sproc. And my solution CAN be done without a single change to anything other than the inside of the called sproc and making a simple sproc on the remote server that is called by that initial sproc. My solution carries a high likelyhood of success - I have seen it over and over in almost 20 years of working with SQL Server. No redesign of any form required.
Now, if you have tons of other cases where have this same execution mechanism those can also be addressed on an as-needed basis, again without any change to the underlying calling app.
Signing off before we get too contentious. I truly wish you the best of luck in solving your performance problem. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service