提高大表内容更新查询性能的最佳途径

Question

我必须执行一个相对非常大的表(80M的记录) Invoice_Payment的更新。它应该更新来自另一个表Invoice_Payment_updated的数据，这个表的行数是Invoice_Payment的10%-15%。为了进行说明，请查看以下演示表：

   Invoice_Payment                         Invoice_Payment_updated
   ---------------                         -----------------------                    
   Customer_id   Invoice_no                Id   Cust_id   Invoice_no      
     10          10100001                  1     10       20200100        
     11          10100002                  2     11       20200101        
     12          10100003
     13          10100004

我知道Merge通常用于执行UPSERT，它的执行时间比同等的Update语句长几倍。但相比之下，在某些情况下，包含多个子查询的普通update语句的性能较低。

MERGE INTO Invoice_Payment ip
USING (SELECT ipu.Cust_id, ipu.Invoice_no from Invoice_Payment_updated ipu
       INNER JOIN Invoice_Payment ip ON ip.Customer_id = ipu.Cust_id
       WHERE ipu.Cust_id = ip.Customer_id and ipu.Invoice_no <> ip.Invoice_no) t
ON (ip.Customer_id = t.Cust_id)
WHEN MATCHED THEN
UPDATE SET ip.Invoice_no = t.Invoice_no;

为了提高性能，我可以使用ROWCOUNT批量更新，但这不会加快执行速度，它只会帮助减少总体锁定。

下面是返回相同输出的简单Update语句：

UPDATE Invoice_Payment 
SET Invoice_no = (SELECT ipu.Invoice_no
                  FROM Invoice_Payment_updated ipu
                  WHERE ipu.Cust_id = Invoice_Payment.Customer_id 
                  AND ipu.Invoice_no <> Invoice_Payment.Invoice_no)

WHERE EXISTS (SELECT 1
              FROM Invoice_Payment_updated ipu
              WHERE ipu.Cust_id = Invoice_Payment.Customer_id
              AND ipu.Invoice_no <> Invoice_Payment.Invoice_no);

使用SQL合并和更新的想法是一个非常聪明的想法，但我听说当我需要更新一个大而宽的表中的许多记录(即超过75M)时，这两种方法都会在性能问题上失败。此外，重新创建整个表需要大量的IO负载，更不用说由于使用子查询而临时多次存储表将占用大量空间。

解决此问题的另一种方法是使用临时表：

CREATE TABLE tmp (
    Cust_id int,
    Invoice_no int);

INSERT INTO tmp_stage VALUES
(SELECT ipu.Cust_id, ipu.Invoice_no FROM Invoice_Payment_updated ipu
 INNER JOIN Invoice_Payment ip ON ip.Customer_id = ipu.Cust_id
 WHERE ipu.Cust_id = ip.Customer_id and ipu.Invoice_no <> ip.Invoice_no);

UPDATE (SELECT tmp.Cust_id, ip.Customer_id, tmp.Invoice_no, tgt.Invoice_no
        FROM tmp INNER JOIN Invoice_Payment ip
        ON tmp.Cust_id = ip.Customer_id)
SET tmp.Invoice_no = ip.Invoice_no;

我想弄清楚在有多个子查询的情况下使用哪一个更好？

欢迎任何想法，并非常感谢对原始问题的完全不同的解决方案。

Answer 1

UPDATE i
SET i.Invoice_no = io.Invoice_no
FROM Invoice_Payment i
   INNER JOIN Invoice_Payment_updated io on i.Customer_id = io.cust_id
WHERE i.Invoice_no <> iu.Invoice_no    -- assuming Invoice_no cannot be NULL

如果更新花费太多时间，请添加WHILE循环和update TOP (10000)直到@@ROWCOUNT = 0。批处理模式可以提高性能。