我可以优化此 SQL 远程更新吗？

Question

我有这个更新远程表的 sql 更新语句。有什么方法可以优化/加速这段代码吗？它作为存储过程的一部分运行。

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)

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

Answer 1

我过去也遇到过类似的问题，我不得不使用动态 SQL 来提高性能。

我发现当我将本地临时表与远程表连接时，SQL 会将所有数据带到本地服务器，然后过滤 where 语句中的内容。

在这种情况下，我将尝试在动态查询中使用 SELECT UNION ALL 将整个变量表 @WIP 作为嵌套表传递。

我正在谈论这样的事情：

 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 

它看起来不太整洁，但它可能对你有用，就像对我一样。

Answer 2

在进行连接之前，我会尝试先将项目加载到本地表变量中。

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 

此外，您可以考虑通过从表变量中删除未更新的记录并仅将更新的记录连接到链接服务器来进一步限制更新。

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

Answer 3

尝试在远程服务器上创建一个由该存储过程调用的存储过程。

在远程存储过程中，将远程服务器上所需的数据拉入临时表中。然后在远程服务器上执行 UPDATE / JOIN。

根据我的经验，尝试跨链接服务器进行联接可能会非常慢......并且在进行任何联接之前在一台服务器或另一台服务器上获取所有所需数据通常会更快。

Answer 4

我发现了查询的真正问题，它更新了数千条记录，即使这些记录没有改变。因此，我查询更改的记录，将其保存在表变量中，并且仅更新更改的记录。整个过程（不仅仅是这部分）从16分44秒缩短到1分26秒。

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

对这个方法有什么评论吗？

Answer 5

PostgreSQL 的 UPDATE 中有一个 ONLY 子句，只会更新提到的表。否则，它会尝试更新您正在加入的所有表，这可能是瓶颈所在。您使用什么类型的 SQL？如果是 Postgres，可能还有其他一些，请尝试将更新行更改为

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