Hi,
I've got a stored procedure which joined some local tables with one table on a linked server, which returns an average of 100 records.
It worked quite nice and quite fast (less then a second to execute), but after the server was updated something strange happened. Half of the time it would perform as it did before, but the other half it would take 10 seconds or more.
When I looked at the trace on the local server there was no difference (except for the execution time), but on the linked server the fast variant executed a procedure for each of the results.
I used sp_recompile on the procedure and since then it always takes 10 seconds or more to execute.
I think the fast method joined the local tables first and then got the data it needed from the linked server.
The slow method probably joins the whole linked server table.
How can I force sqlserver to use the fast method?
Local Server = SQL 2000
Linked Server = SQL 7.0
Hope you can help,
Auke
Start Free Trial
