sql - 如何提高 SQL Server 中合并语句的性能

Question

我必须在具有以下要求的 SQL Server DB 上执行合并语句。

MERGE INTO dbo.cross_charge D
USING
    (
        SELECT
            PROJECT_CODE
            , CROSS_CHARGE_OU
        FROM
            dbo.cross_charge
        WHERE
            PROJECT_CODE = #PROJECT_CODE1
            AND CROSS_CHARGE_OU = #CROSS_CHARGE_OU1
    ) S
ON (
       D.PROJECT_CODE = S.PROJECT_CODE
       AND D.CROSS_CHARGE_OU = S.CROSS_CHARGE_OU
   )
WHEN MATCHED THEN UPDATE SET
                      D.PROJECT_CODE = #PROJECT_CODE3
                      , D.CROSS_CHARGE_OU = #CROSS_CHARGE_OU3
                      , D.CROSS_CHARGE_START = #CROSS_CHARGE_START1
                      , D.CROSS_CHARGE_END = #CROSS_CHARGE_END1
                      , D.IS_Enable = #IS_Enable1
                      , D.UpdatedDate = #UpdatedDate1
WHEN NOT MATCHED THEN INSERT
                          (
                              PROJECT_CODE
                              , CROSS_CHARGE_OU
                              , CROSS_CHARGE_START
                              , CROSS_CHARGE_END
                              , IS_Enable
                              , UpdatedDate
                          )
                      VALUES
                          (
                              #PROJECT_CODE2, #CROSS_CHARGE_OU2, #CROSS_CHARGE_START, #CROSS_CHARGE_END, #IS_Enable, #UpdatedDate
                          )

我必须将 Project_code 和 cross_charge_ou 作为输入参数传递，并根据选择结果插入或更新记录。

当我对 100 条记录执行此查询时，它需要 35 秒，因为我必须为 50K+ 记录执行此语句。

由于我不太了解 SQL，我们是否有任何选项可以提高此查询的性能？

谢谢你。

标签： sqlsql-serverperformancemergequery-optimization