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:
>

Linked Server Error

SQL 2000, Sp3a on Windows 2000. I have a linked server set up to an AS400
using MS OLE DB Provider for DB2, which comes with Host Integration Server.
I have just started to get an error on this LS when trying to use it,
although previously (before Sunday!) it worked fine:
ERROR 7303: Could not initialise data source object of OLE DB provider
'DB2OLEDB'
SqlDumpExceptionHandler: Process 54 generated fatal exception c000005
EXCEPTION ACCESS VIOLATION.SQL Server is terminating this process.
I have bounced the server and checked the event log but it gives me no
additional information. Logon credentials are OK for linked server and I can
ping the box. Nothing new has been installed on the server.
I've got a stack sump generated when the exception occurred if that's useful
to anyone...?
> SQL 2000, Sp3a on Windows 2000. I have a linked server set up to an AS400
> using MS OLE DB Provider for DB2, which comes with Host Integration Server
.
> I have just started to get an error on this LS when trying to use it,
> although previously (before Sunday!) it worked fine:
> ERROR 7303: Could not initialise data source object of OLE DB provider
> 'DB2OLEDB'
> SqlDumpExceptionHandler: Process 54 generated fatal exception c000005
> EXCEPTION ACCESS VIOLATION.SQL Server is terminating this process.
> I have bounced the server and checked the event log but it gives me no
> additional information. Logon credentials are OK for linked server and I c
an
> ping the box. Nothing new has been installed on the server.
> I've got a stack sump generated when the exception occurred if that's usef
ul
> to anyone...?
User submitted from AEWNET (http://www.aewnet.com/)

Linked Server Error

SQL 2000, Sp3a on Windows 2000. I have a linked server set up to an AS400
using MS OLE DB Provider for DB2, which comes with Host Integration Server.
I have just started to get an error on this LS when trying to use it,
although previously (before Sunday!) it worked fine:
ERROR 7303: Could not initialise data source object of OLE DB provider
'DB2OLEDB'
SqlDumpExceptionHandler: Process 54 generated fatal exception c000005
EXCEPTION ACCESS VIOLATION.SQL Server is terminating this process.
I have bounced the server and checked the event log but it gives me no
additional information. Logon credentials are OK for linked server and I can
ping the box. Nothing new has been installed on the server.
I've got a stack sump generated when the exception occurred if that's useful
to anyone...?

> SQL 2000, Sp3a on Windows 2000. I have a linked server set up to an AS400
> using MS OLE DB Provider for DB2, which comes with Host Integration Server.
> I have just started to get an error on this LS when trying to use it,
> although previously (before Sunday!) it worked fine:
> ERROR 7303: Could not initialise data source object of OLE DB provider
> 'DB2OLEDB'
> SqlDumpExceptionHandler: Process 54 generated fatal exception c000005
> EXCEPTION ACCESS VIOLATION.SQL Server is terminating this process.
> I have bounced the server and checked the event log but it gives me no
> additional information. Logon credentials are OK for linked server and I can
> ping the box. Nothing new has been installed on the server.
> I've got a stack sump generated when the exception occurred if that's useful
> to anyone...?
User submitted from AEWNET (http://www.aewnet.com/)

Linked Server Error

SQL 2000, Sp3a on Windows 2000. I have a linked server set up to an AS400
using MS OLE DB Provider for DB2, which comes with Host Integration Server.
I have just started to get an error on this LS when trying to use it,
although previously (before Sunday!) it worked fine:
ERROR 7303: Could not initialise data source object of OLE DB provider
'DB2OLEDB'
SqlDumpExceptionHandler: Process 54 generated fatal exception c000005
EXCEPTION ACCESS VIOLATION.SQL Server is terminating this process.
I have bounced the server and checked the event log but it gives me no
additional information. Logon credentials are OK for linked server and I can
ping the box. Nothing new has been installed on the server.
I've got a stack sump generated when the exception occurred if that's useful
to anyone...?> SQL 2000, Sp3a on Windows 2000. I have a linked server set up to an AS400
> using MS OLE DB Provider for DB2, which comes with Host Integration Server.
> I have just started to get an error on this LS when trying to use it,
> although previously (before Sunday!) it worked fine:
> ERROR 7303: Could not initialise data source object of OLE DB provider
> 'DB2OLEDB'
> SqlDumpExceptionHandler: Process 54 generated fatal exception c000005
> EXCEPTION ACCESS VIOLATION.SQL Server is terminating this process.
> I have bounced the server and checked the event log but it gives me no
> additional information. Logon credentials are OK for linked server and I can
> ping the box. Nothing new has been installed on the server.
> I've got a stack sump generated when the exception occurred if that's useful
> to anyone...?
User submitted from AEWNET (http://www.aewnet.com/)