remote subquery performance & design

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

remote subquery performance & design

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-12 : 09:13:31

dwight writes "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 the performance of joining a local table with the remote table, are there better ways? 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, that would 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...."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources