Can I optimize this SQL remote update?

I have this sql update statement which updates a remote table. Is there any way I can optimize/speed up this code? It is running as part of a stored procedure.

DECLARE @WIP Table(Item varchar(25), WIP int)

--Get Work In Progress Numbers
INSERT INTO @WIP
select (UPC.ItemPrefix + '-' + UPC.ItemCode) As Item, SUM(PO.Quantity) As WIP
--into #WIP
from [NCLGS].[dbo].[AL_ItemUPCs] UPC
INNER JOIN al_PO PO ON PO.UPCID = UPC.UPCID
where PO.sta开发者_如何学运维tus in ('Assigned', 'New', 'UnAssigned', 'WaitingForFile')
Group by  (UPC.ItemPrefix + '-' + UPC.ItemCode)

--SLOW PART, takes over 17 minutes
UPDATE [Server].[Database].[dbo].[Item]  
SET     QtyOnHand = IH.QtyOnHand,
        QtyWorkInProgress = W.WIP   
FROM Avanti_InventoryHeader IH
INNER JOIN [Server].[Database].[dbo].[Item] I ON I.ItemNumber = IH.ItemNumber
LEFT JOIN @WIP W ON IH.ItemNumber = W.Item
WHERE isnumeric(left(IH.ItemNumber, 2)) = 0

I had a similar problem in the past and I had to use dynamic SQL to increase the performance.

I found out that when I joined the local temp table with the remote table SQL was bringing all the data to the local server and then filtering what I had in the where statement.

In this case I would try to pass the whole variable table @WIP as a nested table using SELECT UNION ALL in a dynamic query.

I am talking about something like this:

 DECLARE @WIP Table(Item varchar(25), WIP int)

    --Get Work In Progress Numbers
    INSERT INTO @WIP
    select (UPC.ItemPrefix + '-' + UPC.ItemCode) As Item, SUM(PO.Quantity) As WIP
    --into #WIP
    from [NCLGS].[dbo].[AL_ItemUPCs] UPC
    INNER JOIN al_PO PO ON PO.UPCID = UPC.UPCID
    where PO.status in ('Assigned', 'New', 'UnAssigned', 'WaitingForFile')
    Group by  (UPC.ItemPrefix + '-' + UPC.ItemCode)

declare @SQL VARCHAR(MAX)

set @SQL = '
UPDATE [Server].[Database].[dbo].[Item]  
SET     QtyOnHand = IH.QtyOnHand,
        QtyWorkInProgress = W.WIP   
FROM Avanti_InventoryHeader IH
INNER JOIN [Server].[Database].[dbo].[Item] I 
    ON I.ItemNumber = IH.ItemNumber
LEFT JOIN ('

select @SQL = @SQL + 'select '''+w.Item+''' as Item, 
    '''+cast( w.WIP as varchar(50))+''' as WIP union all '
from @WIP W

set @SQL = @SQL + ' select NULL,0 ) W   
    ON IH.ItemNumber = W.Item
WHERE isnumeric(left(IH.ItemNumber, 2)) = 0 '

PRINT @SQL 

It does not look very neat, but it might work for you as it did for me.

I'd try loading the items into a local table variable first before doing the join.

DECLARE @WIP Table ( Item varchar(25), WIP int )
  --Get Work In Progress Numbers 
INSERT  INTO @WIP
        select  ( UPC.ItemPrefix + '-' + UPC.ItemCode ) As Item,
                SUM(PO.Quantity) As WIP 
        --into #WIP 
        from    [NCLGS].[dbo].[AL_ItemUPCs] UPC
                INNER JOIN al_PO PO ON PO.UPCID = UPC.UPCID
        where   PO.status in ( 'Assigned', 'New', 'UnAssigned',
                               'WaitingForFile' )
        Group by ( UPC.ItemPrefix + '-' + UPC.ItemCode )  

DECLARE @Item TABLE (ItemNumber  INT PRIMARY KEY, QtyOnHand INT)
SELECT ItemNumber, QtyOnHand
FROM   [Server].[Database].[dbo].[Item]    

--SLOW PART, takes over 17 minutes 
UPDATE  [Server].[Database].[dbo].[Item]
SET     QtyOnHand = IH.QtyOnHand,
        QtyWorkInProgress = W.WIP
FROM    Avanti_InventoryHeader IH
        INNER JOIN @item I ON I.ItemNumber = IH.ItemNumber
        LEFT JOIN @WIP W ON IH.ItemNumber = W.Item
WHERE   isnumeric(left(IH.ItemNumber, 2)) = 0 

Additionally you could consider further further restricting the update by removing records from the table variable that aren't being updated and join only the updated records to the linked server.

DECLARE @Item TABLE
    (
      ItemNumber INT PRIMARY KEY,
      QtyOnHand INT,
      updated BIT DEFAULT ( 0 ),
      WIP int
    )
SELECT  ItemNumber,
        QtyOnHand
FROM    [Server].[Database].[dbo].[Item] 

UPDATE  i
SET     i.QtyOnHand = ih.QtyOnHand,
        updated = 1
FROM    @item i
        INNER JOIN Avanti_InventoryHeader IH ON I.ItemNumber = IH.ItemNumber
        LEFT JOIN @WIP W ON IH.ItemNumber = W.Item
WHERE   isnumeric(left(IH.ItemNumber, 2)) = 0  

DELETE FROM @item WHERE updated = 0

UPDATE  I
SET     QtyOnHand = IH.QtyOnHand,
        QtyWorkInProgress = IH.WIP
FROM    [Server].[Database].[dbo].[Item] I
        INNER JOIN @item IH ON I.ItemNumber = IH.ItemNumber

Try creating a stored procedure on the remote server that is called by this stored procedure.

In the remote stored procedure, pull the data that you need onto the remote server into temporary tables. Then perform the UPDATE / JOIN on the remote server.

In my experience, trying to do a join across a linked server can be very slow... and it is often faster to get all the required data on one server or the other before doing any joining.

I figured out the real problem with the query, it was updating thousands of records, even if those records didn't change. So I queried for the changed records, saved that in a table variable, and only updated the changed records. Entire procedure (not just this part) went from 16min 44sec to 1min 26 sec.

--BEGIN EXPERIMENTAL ITEM UPDATE SECTION
    DECLARE @WIP Table(Item varchar(25), WIP int)

    --Get Work In Progress Numbers
    INSERT INTO @WIP
    select (UPC.ItemPrefix + '-' + UPC.ItemCode) As Item, SUM(PO.Quantity) As WIP
    --into #WIP
    from [NCLGS].[dbo].[AL_ItemUPCs] UPC
    INNER JOIN al_PO PO ON PO.UPCID = UPC.UPCID
    where PO.status in ('Assigned', 'New', 'UnAssigned', 'WaitingForFile')
    Group by  (UPC.ItemPrefix + '-' + UPC.ItemCode)

    Declare @Remote Table(Item varchar(25), QtyOnHand int, WIP int)

    INSERT INTO @REMOTE
    Select ItemNumber, QtyOnHand, QtyWorkInProgress
    from [BMPR.COM].[NCL-CustomerPortal].[dbo].[Item]

    DECLARE @ItemsToUpdate Table (Item varchar(50))

    INSERT INTO @ItemsToUpdate
    Select R.Item
    From @Remote R
    Inner join Avanti_InventoryHeader IH ON R.Item = IH.ItemNumber
    LEFT JOIN @WIP W ON R.Item = W.Item
    Where R.QtyOnHand <> IH.QtyOnHand 
    OR R.WIP <> W.WIP

    --Select * from @ItemsToUpdate

    UPDATE [BMPR.COM].[NCL-CustomerPortal].[dbo].[Item]  
    SET     QtyOnHand = IH.QtyOnHand,
            QtyWorkInProgress = W.WIP   
    FROM Avanti_InventoryHeader IH
    INNER JOIN [BMPR.COM].[NCL-CustomerPortal].[dbo].[Item] I ON I.ItemNumber = IH.ItemNumber
    LEFT JOIN @WIP W ON IH.ItemNumber = W.Item
    WHERE I.ItemNumber in ( Select * from @ItemsToUpdate )
--END EXPERIMENTAL ITEM UPDATE SECTION

Any comments on this method?

There is an ONLY clause in PostgreSQL's UPDATE that will only update the table mentioned. Otherwise it tries to update all the tables you're joining and that could be where the bottleneck is. What type of SQL are you using? If it is Postgres, are possibly some others, try changing the update line to

UPDATE ONLY [Server].[Database].[dbo].[Item]