update large table et verrous

bonjour.

Comment améliorer les performance d'un update sur une table de 20 millions de records en utilisant les options de lock ?

Aucun autre accès ne fait des mises à jour.
La table REF ne contient que 10 lignes.

Cela prend une heure pour un update du style.

UPDATE STG_PRESTATIONS WITH (TABLOCK)
SET
IDDestination_facture = ref.IDDestination_facture
FROM
STG_PRESTATIONS stg,
REF_FLSF ref
WHERE
stg.flsf = ref.flsf

Merci à tous.

Pourquoi ne pas utiliser la notion de transaction sérialialisable ?

hello,

J'ai plus ou moins le mm problème .. (avec 600 000 lignes sur 2 serveurs distribués)

heu apaprement la trnasaction serializable est ce qui "mange" le plus de ressources mais bon je l'utilise dans mon update car c'est tt mm bcp plus sécurisé qu'un lock..
je vais essayer en baissant le niveau de la transaction mais je voudrais bien eviter : (

si quelqu'un a des optimisations classiques à proposer pour ce genre de chose je suis preneur aussi : )

je cherche pour l'instant..

Bien au contraite, si vous voulez des perf, il faut descendre le verrouillage ! pas le monter en TABLOCK !!!!!

laissez faire l'optimiseur et pissez un requête la plus simple possible et avec des JOIN :

Code:

---

1 2 3 4 5

UPDATE STG_PRESTATIONS SET IDDestination_facture = ref.IDDestination_facture FROM STG_PRESTATIONS stg INNER JOIN REF_FLSF ref ON stg.flsf = ref.flsf

---

Les colonnes stg.flsf et ref.flsf sont-elles indexées ? J'espère que oui ! Sachez que MS SQL Server n'index pas automatiquement les clef étrangères.

La colonne STG_PRESTATIONS est-elle indexée ? J'espère que non.

Dans le cas ou vous ne vous souciez pas de l'intégrité d'instant de l'exécution de l'update, alors veuillez procéder par lot de ligne. Cherchez le nombre moyen de ligne dont le volume correspondra à l'extent de stockage.

exemple; si la longueur moyenne de la ligne cible de la table visée est de 200 octets, alors procédez par lots de 320 lignes (un extent = 8 pages de données, soit 8 * 8 Ko).

Dès lors procédez comme suit :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

DECLARE @IDMIN INT, @IDMAX INT   WHILE EXISTS (SELECT * FROM STG_PRESTATIONS stg INNER JOIN REF_FLSF ref ON stg.flsf = ref.flsf WHERE stg.IDDestination_facture <> ref.IDDestination_facture) BEGIN --> sélectionnez TOP 320 lignes à mettre à jour dans l'ordre de la clef et retenez l'id max --> appliquez l'update restreint à la tranche :   UPDATE STG_PRESTATIONS SET IDDestination_facture = ref.IDDestination_facture FROM STG_PRESTATIONS stg INNER JOIN REF_FLSF ref ON stg.flsf = ref.flsf WHERE stg.id BETWEEN @IDMIN AND @IDMAX-1   --> alimentez @DIMIN avec @IDMAX SET @IDMIN = @IDMAX   END

---

A +

Assurez-vous qu'un index existe sur stg.flsf, que les deux colonnes
stg.flsf et ref.flsf soient exactement de même type et queles statistiques soient à jur. Au besoin, exécutez un update statistics + sp_recompile sur votre grosse table (attention : à faire de nuit ou le we, hors traitements batch)