Linked Server Performance with Stored Procedure

I have set up a stored procedure to do a quick lookup similar to
dbo.get_Value_for_Id @.id int

When I invoke the stored procedure from a database on the same server instance as the stored procedure lives, it processes 10K lookups in about 4 seconds.

When I invoke the stored procedure from a different server (via MSSM with a Linked server), it took about 4 minutes to perform 10K lookups. Not good. My question is what can I do to get back to the much faster response times.

I set up a little .Net app to do the remote calls, and it was back to the 4 second range for the 10K calls, in my mind eliminating the network as the primary bottleneck.

Items I've tried:

SET XACT_ABORT is OFF on both ends. No visible results.

SET REMOTE_PROC_TRANSACTIONS OFF . This appears to have put the distributed transactions issue to bed.

Run SP_SERVEROPTION "collation compatible" option to true for the server link. Ran this on both ends. This setting supposedly tells SQL Server the collation sequence and character sets match on both ends. Probably a good idea, even though it didn't make much difference here.

Run the stored procedure remotely using OpenQuery. Took some creativity, given OpenQuery doesn't accept variables as arguments. Got this one working, with run times down to the 90-135 second range (1:30-1:45).

I'm still an order of magnitude slower than I want to be. I suppose I could implement a .NET solution (select the key out of a database on server x, lookup the key on server y), but that sounds more like a workaround than actually using SQL Server capabilities. Any suggestions?

try

exec linkedserver.mydb.dbo.sp_executesql N'exec mysp'

|||

Thanks for the suggestion, Nigel.

I setup two variations of the sp_executesql call, one calling the stored procedure, the second invoking the main select in the stored procedure. In both cases, when run during an MSSM session that was logged into the server that was doing the work, I could loop through 10000 calls in 4-5 seconds. And, in both cases, when logged into my desktop, it took about 4 and a half minutes to run the same script. So this didn't resolve the issue. Interestingly enough, I did see some surging in the activity of my desktop msdtc.exe (in windows task manager), so I'm guessing this process is somehow getting the distributed transaction server involved.

To confirm the involvement of the distributed transaction server, I went into my desktop and turned off the msdtc service. With this service turned off, the script would not run (MSG 8501 MSDTC on server xxx not available). So that seems to confirm I have not evaded the overhead of MSDTC.

To minimize confusion, following is the test script I am using...

set nocount on

set XACT_Abort off

set REMOTE_PROC_TRANSACTIONS off

declare @.Encrypt_id int

declare @.c int

declare @.t table (UnEncrypted_Card varchar(19))

declare @.sql nvarchar(500)

declare @.ParamDef nvarchar(500)

set @.c = 0

while (@.c < 10000)

Begin

set @.Encrypt_id = Rand() * 100000

set @.sql = N'exec dbo.p_Get_Card_Number_By_Id_inst @.Enc_id , ''123456'' ';

-- set @.sql = N'select Value_Encrypted as Value from dbo.tb_Encrypted_Store where Encrypt_Id = @.Enc_Id';

set @.ParamDef = N'@.Enc_Id int';

-- print @.sql

insert into @.t exec linkservername.dbname.dbo.sp_executesql @.sql, @.ParamDef, @.Enc_id = @.Encrypt_Id

set @.c = @.c + 1;

End