Meilleure approche pour améliorer les performances des requêtes de mise à jour du contenu d'une grande table

Je dois effectuer la mise à jour d'une table relativement très grande (80M d'loggings) Invoice_Payment . Il devrait mettre à jour datatables d'une autre table Invoice_Payment_updated qui représente 10% -15% de Invoice_Payment dans le nombre de lignes. Pour illustrer s'il vous plaît jeter un oeil aux arrays de démonstration suivants:

  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 

Je sais que Merge est généralement utilisé pour exécuter un UPSERT et qu'il faut plusieurs fois plus de time pour s'exécuter que l'instruction Update équivalente. Mais en comparaison, il y a des cas où une instruction de mise à jour normale avec plusieurs sous-requêtes donne des performances inférieures.

 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; 

Pour améliorer les performances, je peux charger les mises à jour en utilisant ROWCOUNT, mais cela n'accélérera pas l'exécution, cela ne fera que réduire le locking global.

Après une simple instruction Update qui renvoie la même sortie:

• SSIS ne peut pas convertir entre unicode et non-unicode
• L'access à SQL Server donne une erreur – ORA-12154: TNS: impossible de résoudre l'identificateur de connection spécifié
• Comment créer une colonne fictive dans SSIS 2008
• Fournisseur Oracle OLE DB non répertorié dans SSIS
• Calcul du time de trajet dans les bases de données relationnelles?

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

L'idée d'utiliser SQL Merge et Update est très astucieuse, mais j'ai entendu dire que tous deux échouent dans les problèmes de performances quand j'ai besoin de mettre à jour de nombreux loggings (plus de 75M) dans une grande table. En outre, recréer la table complète request beaucoup d'IO, sans parler du fait qu'il faudra beaucoup d'espace pour que la table soit stockée plusieurs fois temporairement en raison de l'utilisation de sous-requêtes.

Une autre approche pour résoudre ce problème en utilisant une table temporaire:

 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; 

Je veux savoir lequel est le meilleur à utiliser en cas de plusieurs sous-requêtes?

Toutes les pensées sont les bienvenues et une solution totalement différente au problème original est très appréciée.

• Plate-forme Déclaration SQL neutre pour INSTR ou CHARINDEX
• Vanilla SQL avec sous-requête corrélée fonctionne en T-SQL, échoue en PL / SQL
• Conversion de type de données de SQL Server à Oracle
• accéder aux données Oracle à partir de SQL Server
• Convertir le script ansi en unicode automatiquement

 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