sql-server - SQL NOT EXISTS 在 OpenQuery 上太慢

Question

我目前使用 NOT EXISTS 从 db2 远程服务器复制数据以保持本地表的更新。该程序每周仅在 1 天内执行一次。问题是，如果它没有被缓存，它会花费超过 40 秒的时间来执行它。

基本上我需要按 2 列插入记录，参考和 UAP（生产单元），其中 1 个参考可以有许多 UAP。我假设查询会因为 where 子句的两个条件而变慢。

ALTER PROCEDURE CopyReferenciasFromPDP
AS
BEGIN

    DECLARE @NumTurnos int, 
            @NumPab int,
            @AlcanceAbastecimento int,
            @QtdMin int,
            @QtdMax int

    SET @NumTurnos = 3
    SET @NumPab = 6
    SET @AlcanceAbastecimento = 3
    SET @QtdMin = 3
    SET @QtdMax = 12

    INSERT INTO Parametros  
    SELECT
        Referencia,     
        UAP,
        @NumTurnos AS NumTurnos,
        @NumPab AS NumPAB,
        @AlcanceAbastecimento AS AlcanceAbastecimento,
        @QtdMin AS QtdMin,
        @QtdMax AS QtdMax
    FROM OPENQUERY(MACPAC, 
        'SELECT 
            A.RH6001 as Referencia, 
            A.RH6002 as UAP
        FROM AUTO.D805DATPOR.TRP060H AS A       
        WHERE (A.RH6001 Not Like ''FS%'')
        AND A.RH6030 <> 0') M
    WHERE
        NOT EXISTS 
        ( 
            SELECT * FROM Parametros P  
            WHERE M.Referencia = P.Referencia 
            AND M.UAP = P.UAP 
        )

END

我怎样才能提高性能？或者有没有更快的替代方案来替代不存在的地方？

此外，openquery 每周平均返回 2k 条记录，但大多数记录总是相同的。

最后结果

这是最终的解决方案。我使用了@scsimon 建议的表变量

ALTER PROCEDURE CopyReferenciasFromPDP
AS
BEGIN

    DECLARE @NumTurnos INT, 
            @NumPab INT,
            @AlcanceAbastecimento INT,
            @QtdMin INT,
            @QtdMax INT

    SET @NumTurnos = 3
    SET @NumPab = 6
    SET @AlcanceAbastecimento = 3
    SET @QtdMin = 3
    SET @QtdMax = 12

    CREATE TABLE #tempTable 
    (
        Id INT PRIMARY KEY,
        Referencia VARCHAR(15),
        UAP NVARCHAR(20),
        NumTurnos INT,
        NumPab INT,
        AlcanceAbastecimento INT,
        QtdMin INT,
        QtdMax INT
        UNIQUE (Id)
    )

    INSERT INTO #tempTable  
    SELECT
        ROW_NUMBER() OVER(ORDER BY Referencia ASC),
        Referencia,     
        UAP,
        @NumTurnos AS NumTurnos,
        @NumPab AS NumPAB,
        @AlcanceAbastecimento AS AlcanceAbastecimento,
        @QtdMin AS QtdMin,
        @QtdMax AS QtdMax
    FROM OPENQUERY(MACPAC, 
        'SELECT 
            A.RH6001 as Referencia, 
            A.RH6002 as UAP
        FROM AUTO.D805DATPOR.TRP060H AS A       
        WHERE (A.RH6001 Not Like ''FS%'')
        AND A.RH6030 <> 0') 


    INSERT INTO 
        Parametros
    SELECT 
        Referencia, 
        UAP,        
        NumTurnos,
        NumPab,
        AlcanceAbastecimento,
        QtdMin,
        QtdMax
    FROM 
        #tempTable M
    WHERE
        NOT EXISTS 
        ( 
            SELECT * FROM Parametros P  
            WHERE M.Referencia = P.Referencia 
            AND M.UAP = P.UAP
        )

    DROP TABLE #tempTable
END

编辑

将表变量更改为临时表

标签： sql-server