Discussion :

[pascalste]

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.

[WOLO Laurent]

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

[bran_noz]

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..

[SQLpro]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 +

[Fabien Celaia]

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)