Linked server performance

SQL Server 2000 Standard, SP3a. I have a linked server
defined pointing to DB2, using their DB2 Connect driver.
When I execute a single table query from SQL Server
against the DB2 linked server with a highly selective
where clause using four part naming, it appears as if the
query retrieves all rows from the remote DB2 table into
SQL Server, and then applies the filter. Is there any way
to force the filtering to occur on the remote database?
examples:
runs fast:
select * from OPENQUERY(DB2Server, 'select COL1, COL2
from SCH.TABLE where COL1 = ''4''')
runs slow:
select COL1, COL2 from DB2Server..SCH.TABLE
where COL1 = '4'
Thanks
Vern RabeI don't understand your question. You seemed to have answered it yourself.
If you use OPENQUERY() it will just pass the query through to the remote
server and return the results. If you do not use OPENQUERY it may return
lots of rows due to the way the linked server and the optimizer works. You
obviously found that to be the case so what is wrong with using the first
method?
--
Andrew J. Kelly SQL MVP
"Vern Rabe" <vern@.NO_rabe_SPAM.com> wrote in message
news:0adf01c53c93$fce71970$a501280a@.phx.gbl...
> SQL Server 2000 Standard, SP3a. I have a linked server
> defined pointing to DB2, using their DB2 Connect driver.
> When I execute a single table query from SQL Server
> against the DB2 linked server with a highly selective
> where clause using four part naming, it appears as if the
> query retrieves all rows from the remote DB2 table into
> SQL Server, and then applies the filter. Is there any way
> to force the filtering to occur on the remote database?
> examples:
> runs fast:
> select * from OPENQUERY(DB2Server, 'select COL1, COL2
> from SCH.TABLE where COL1 = ''4''')
> runs slow:
> select COL1, COL2 from DB2Server..SCH.TABLE
> where COL1 = '4'
> Thanks
> Vern Rabe

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