Unanswered: Stored procedure that calls linked server slow.
I have a stored procedure that runs a select on a linked server. The stored procedure takes a parameter that is used in the query to the linked server. It is the primary key of the table being queried, so only one row will be returned. Below is an example:
select * from linkedserver.db.dbo.table where primary_key = 1234
But in the stored proc, it takes 7 minutes using this:
select * from linkedserver.db.dbo.table where primary_key = @pk
Both servers are running SQL2K Enterprise Service Pack 3.
I created a new proc that only selects the one row, and I get the same results. The table is not locked. Basically, what is happening is a table scan becuase the query is not using the index for the primary key.
This query worked fine a few weeks ago when we ran it on SQL2K standard agains SQL 2K enterprise. But now that we have moved it to production, where both servers are on SQL2K enterprise, it doesnt work.
Unfortunatley, these two servers will be talking to each other quite a bit, so I need to be able to correct the actual problem rather than workaround it. Since it was working before, I know it can be done...I just need to figure out why the new server doesnt do it right!
I create a new linked server, but instead of selecting SQL server and entering the servername, select the OLEDB Provider for SQL Server instead. Click the button called Provider options and check the box for "Dynamic Parameters" and click Apply. Then click cancel on the screen for the new linked server.