SQL query design & performance

[rocker]

SETUP: I have one local table (MAIN_TBL) and one linked server AS (rmt). I need dates from the remote table (rmt) to go with the list of order numbers already in MAIN_TBL. I'm concerned about how linking a local table with a remote table will work & perform. So thats the main goal. One other problem I haven't gotten past...the remote table also contains order versions numbered 0,1,2,3,4,etc. So one order# has say three rows version 0,1,2 each with a different date. I only need the MAX version number data pulled which will be the latest date.

UPDATE MAIN_TBL
SET MAIN_TBL.Date_Rtn = rmt.CUS_DATE
FROM (
SELECT LnkS.ORDER_VER, LnkS.CUS_DATE, LnkS.ORD_NBR
FROM LinkServer..DB.TableName AS LnkS
INNER JOIN MAIN_TBL AS mt ON mt.order# = LnkS.ORD_NBR
WHERE LnkS.CUS_DATE IS NOT NULL
) AS rmt
INNER JOIN MAIN_TBL ON MAIN_TBL.order# = rmt.ORD_NBR

Any help would be appreciated....

[skhanal]

Joining with remote table like this is usually very slow, especially when you do not have any filter. Performance also depends on how many rows are in each tables.

[rocker]

Thats what I was afraid of but then how should I go about it?

Joining with remote table like this is usually very slow, especially when you do not have any filter. Performance also depends on how many rows are in each tables.