Linked Server Performace dip

Hi -
We have designed an Integration Architecture with SQL Linked Servers and the
layout will be a single SQL Server(Publisher) connecting to multiple Linked
Servers (Subsribers).
We have a bottle-nect in the performance when we try to execute an update
statement, which takes 30 mins for 16 records. It is a simple update
statement, present inside a local cursor
<<
update NCCHRASQLAPP30_DATA_02.data_02.dbo.AROPNFIL_SQL set filler_0001 =
@.status where doc_no = @.invoice
When I replace the @.status with a hard-coded value (say 'P'), then procedure
runs very fast.
Is this related to Collation Compatibility. Pls. let me know what could be
possible glitch.
Attaching the primary part of the Proc if you need it for review.APP30 is
the linked server.
<<<<<
CREATE procedure dbo.Process_Test
as
declare
@.invoice varchar(10) , @.reason varchar(500), @.status
char(2) , @.v_apptranscd varchar(50), @.v_mailboxid varchar(15
)
begin
SET XACT_ABORT ON
SET NOCOUNT ON
declare reject_invc_cursor CURSOR for
select Invc_Nmbr,Reason,upper(ar_stts),app_tran
s_cd,mailbox_Id from
dbo.INVOICE_INBOUND where upper(ar_stts) in('E','A','W','R') for read only
open reject_invc_cursor
FETCH NEXT FROM reject_invc_cursor INTO @.invoice, @.reason , @.status ,
@.v_apptranscd, @.v_mailboxid
IF @.@.FETCH_STATUS = 0
BEGIN
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.v_apptranscd = 'MCLNC079_AR'
update APP30_DATA_02.data_02.dbo.AROPNFIL_SQL set filler_0001 =
@.status where doc_no = @.invoice
else if @.v_apptranscd = 'MCLIL089_AR'
update APP30_DATA_02.data_02.dbo.AROPNFIL_SQL set filler_0001 =
@.status where doc_no = @.invoice
else if @.v_apptranscd = 'MCLCA089_AR'
update APP30_DATA_02.data_02.dbo.AROPNFIL_SQL set filler_0001 =
@.status where doc_no = @.invoice
FETCH NEXT FROM reject_invc_cursor INTO @.invoice, @.reason ,@.status
, @.v_apptranscd, @.v_mailboxid
END
END
CLOSE reject_invc_cursor
DEALLOCATE reject_invc_cursor
commit
select 1
return 1
Thanks/Shriram.Hi Mark -
Thanks for a quick response. But I cannot chartout in a single query as
based on the ID it should update different Linked Servers.
As a work around, I moved the status as part of the IF condition and
hard-coded in the query, it works extremely fast...
Thanks/Shriram.
"Mark Williams" wrote:
> To see the best performance increase, lose the cursor and go with a set-ba
sed
> approach:
> UPDATE APP30_DATA_02.data_02.dbo.AROPNFIL_SQL
> SET filler_0001 = t2.status
> FROM
> APP30_DATA_02.data_02.dbo.AROPNFIL_SQL t1
> INNER JOIN
> (
> SELECT Invc_Nmbr, upper(ar_stts) AS "status" FROM dbo.INVOICE_INBOUND
> WHERE upper(ar_stts) IN ('E','A','W','R')
> AND app_trans_cd IN ('MCLNC079_AR','MCLIL089_AR','MCLCA089_A
R')
> ) t2
> ON t1.doc_no = t2.Invc_Nmbr
>
> --
>
> "shriram2977" wrote:
>