December 5, 2013 at 6:35 pm
I have a fairly long query that joins tables on residing on different physical servers.
While using linked servers works, I'm trying to replace linked servers with the exec ('query') at option as the latter is significantly faster.
However, I'm not sure if it's possible when you have a query with subqueries and the 'WITH" clause hitting several physical servers.
Thank you for suggestions.
Len
PS. I want to avoid rewriting this as a stored proc for now.
December 6, 2013 at 12:44 am
You should pull the data over the linked servers into a temp table, then join to that. When you join across the link, the local SQL server cannot evaluate the remote server statistics causing a bad plan. Check that the linked servers are setup with the option "Collation Compatible=True" (check that they are). That will allow the local query to run remotely and the resultset will be returned to the local server to be stored in the temp table. If that option is false, the entire table is pulled over the link then filtered according to your query.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply