December 19, 2008 at 12:52 pm
I am running a stored procedure that accesses a lot of data through a linked server. When I run a trace on the proc when it's executed, it shows a table scan of two tables on the destination server with millions of rows in it. When I run the pertinent code on the destination server the trace shows the index being used and it returns immediately. Is there any reason an index would not be used just because the table is being accessed through a linked server?
December 20, 2008 at 9:39 am
If you use a linked server to query data it seems that it pulls all of the data back locally to the calling machine and then filters from there, as such it will perform a table scan on the remote server with it being the most efficient way of getting the data.
Rather than use the full 4 part naming convention try using an openquery to the remote server and pass the results back to a temp table on the local server. By doing this all of the hard work is done on the remote side. The query will be evaluated on the remote server and an execution plan created. Just the results are then passed back to the local server rather than everything.
Doing this in the past I have found magnitudes of performance improvements in linked server queries.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply