Discussion :

[Kropernic]

Hello,

Je m'arrache les cheveux ici sur procédure qui "tourne en boucle" (en fait je ne sais pas ce qu'elle fait mais elle ne s'arrête pas et je dois killer son process pour débloquer mon serveur de test).

Voici son code.

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
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
USE [INNO_ADMIN]
GO
/****** Object:  StoredProcedure [S_PROMO].[UP_PROMO_SAVE_DEMO]    Script Date: 27/01/2015 17:08:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
 
ALTER PROCEDURE [S_PROMO].[UP_PROMO_SAVE_DEMO]
    @PRM_ID                INT,
    @DETAILS_DEMO        DETAILS_DEMO READONLY,
    @BA_ID                TINYINT,
    @PRODUCTS            PRODUCT_LIST READONLY,
    @PCT_ID                TINYINT
AS
BEGIN
    SET NOCOUNT ON;
 
    BEGIN TRAN
 
    DECLARE @ERROR INT;
 
    MERGE S_PROMO.T_DETAIL_DEMO_DTD AS TARGET
    USING (    SELECT  CONT_ID, DTD_PERCENT, DTD_DESC, DTD_NOTE
            FROM    @DETAILS_DEMO ) AS SOURCE(CONT_ID, DTD_PERCENT, DTD_DESC, DTD_NOTE)
        ON    TARGET.PRM_ID = @PRM_ID
        AND    TARGET.CONT_ID = SOURCE.CONT_ID
        AND TARGET.DTD_PERCENT = SOURCE.DTD_PERCENT
    WHEN MATCHED THEN
        UPDATE    SET    DTD_PERCENT = SOURCE.DTD_PERCENT,
                    DTD_DESC    = SOURCE.DTD_DESC,
                    DTD_NOTE    = SOURCE.DTD_NOTE
    WHEN NOT MATCHED THEN    
        INSERT(PRM_ID, CONT_ID, DTD_PERCENT, DTD_DESC, DTD_NOTE, BA_ID, PCT_ID)
        VALUES(@PRM_ID, SOURCE.CONT_ID, SOURCE.DTD_PERCENT, SOURCE.DTD_DESC, SOURCE.DTD_NOTE, @BA_ID, @PCT_ID);
 
    SET @ERROR = @ERROR + @@error;
 
    MERGE S_PROMO.T_PRODUCT_LIST_DEMO_PLD AS TARGET
    USING (    SELECT    DTD.DTD_ID,
                    P.PLD_BARCODE,
                    P.PLD_INCLUSIVE,
                    P.PLD_COLOR,
                    P.PLD_SIZE,
                    P.PLD_DESC,
                    P.PLD_PRICE
            FROM    @PRODUCTS P
                        INNER JOIN S_PROMO.T_DETAIL_DEMO_DTD DTD
                            ON    DTD.PRM_ID = @PRM_ID
                            AND    DTD.CONT_ID = P.CONT_ID
                            AND DTD.DTD_PERCENT = P.DTD_PERCENT) AS SOURCE(DTD_ID, PLD_BARCODE, PLD_INCLUSIVE, PLD_COLOR, PLD_SIZE, PLD_DESC, PLD_PRICE)
        ON    TARGET.DTD_ID = SOURCE.DTD_ID
        AND TARGET.PLD_BARCODE = SOURCE.PLD_BARCODE
    WHEN MATCHED THEN
        UPDATE    SET    PLD_INCLUSIVE = SOURCE.PLD_INCLUSIVE,
                    PLD_COLOR = SOURCE.PLD_COLOR,
                    PLD_SIZE = SOURCE.PLD_SIZE,
                    PLD_DESC = SOURCE.PLD_DESC,
                    PLD_PRICE = SOURCE.PLD_PRICE
    WHEN NOT MATCHED THEN
        INSERT(DTD_ID, PLD_BARCODE, PLD_INCLUSIVE, PLD_COLOR, PLD_SIZE, PLD_DESC, PLD_PRICE)
        VALUES(SOURCE.DTD_ID, SOURCE.PLD_BARCODE, SOURCE.PLD_INCLUSIVE, SOURCE.PLD_COLOR, SOURCE.PLD_SIZE, SOURCE.PLD_DESC, SOURCE.PLD_PRICE);
 
    SET @ERROR = @ERROR + @@error;
 
    DELETE FROM    S_PROMO.T_PRODUCT_LIST_DEMO_PLD
    WHERE    DTD_ID IN (    SELECT    DTD.DTD_ID
                        FROM    @PRODUCTS P
                                    INNER JOIN S_PROMO.T_DETAIL_DEMO_DTD DTD
                                        ON    DTD.PRM_ID = @PRM_ID
                                        AND    DTD.CONT_ID = P.CONT_ID
                                        AND DTD.DTD_PERCENT = P.DTD_PERCENT)
        AND PLD_BARCODE NOT IN (SELECT    PLD_BARCODE
                                FROM    @PRODUCTS)
 
    SET @ERROR = @ERROR + @@error;
 
    IF @ERROR <> 0
    BEGIN
        DECLARE @MSG AS VARCHAR(1000) = 'ERROR DURING SAVING DETAILS DEMO';
        RAISERROR(@MSG,16,1)
        ROLLBACK TRAN
    END
    ELSE
        COMMIT TRAN
END

Bon alors, je vous accorde que c'est un peu long mais la bonne nouvelle, c'est que je sais que le problème se situe soit dans le dernier MERGE, soit dans le DELETE qui suit car ils ne font vraiment quelque chose que si le tvp nommé @PRODUCTS contient au moins une ligne. Or j'ai fait tous les tests ce matin sur les cas où @PRODUCTS est vide et tout était positif. Ce n'est que depuis cet après-midi que je mets des lignes dans @PRODUCTS et que ça pédale dans la semoule.

Je m'adresse à vous car ça fait 3h que je regarde cette procédure sous tous les angles et que je ne vois pas ce qu'il cloche. A mon avis, à force d'être le nez dessus, je n'ai plus assez de recul pour voir l'évidence. Car comme d'hab, j'suis sûr que ça va être un truc à la con qui fout le bordel...

Merci d'avance.

P.S. : Je n'ai pas posté de DDL car le code de la procédure est déjà assez long mais si vous en voulez, suffit de demander ^^.

EDIT : Dans le doute, je vais la lancer et aller boire un café. On ne sait jamais que ce serait juste vraiment long à s'exécuter...

[SQLpro]

commence par regarder le plan de requête.

lance là toute seule dans une base de test

puis lance là en production avec le profiler et une trace de verrouillage :
DBCC TRACEON (3604);
DBCC TRACEON (1200, -1);

A +

[aieeeuuuuu]

Bonjour,

Aucune chance qu'elle ne tourne en boucle, il n'y a aucune structure de contrôle...

Elle peut être bloquée par un autre processus, par exemple si tu as une transaction encore ouverte qui verrouille l'un des objets accédés par ta procédure (cas classique en développement).

Elle peut aussi être tout simplement longue. Tu utilises des variables de type table. Pour ce type de table, l'optimiseur considère souvent qu'elle ne contient qu'une seule ligne. combien en contiennent-elles dans ton cas?
Si c'est beaucoup plus élevé, les plans d’exécution peuvent être très mauvais, ce qui pourrait expliquer ton problème. Tu peux dans un premier temps ajouter l'option OPTION(RECOMPILE) à chaque requête pour forcer l'optimiseur à évaluer correctement le nombre de lignes dans les tables variables et voir si le problème vient de là (ensuite, d'autres corrections plus efficace pourront être mise en place).

sinon... il faudra tracer pour comprendre d'où vient le problème.

[Kropernic]

Hello,

Je testerai cela demain au boulot mais il y a effectivement bien plus qu'une ligne dans le tvp @PRODUCTS (les autres, ça va, genre 5 max). Ici pour le test qui m'occupe, j'ai un peu plus de 10000 lignes dans @PRODUCTS alors oui, s'il prend 1 comme estimation, ça risque fort d'être le problème.

Merci pour la piste !!

[Kropernic]

Hello,

J'ai testé OPTION(RECOMPILE) mais sans succès.

Ensuite, j'ai fait comme pour un cas de parameter sniffing "classique" et j'ai donc réaffecté les paramètres dans des variables locales à la procédure.

Là j'ai vu que c'était le DELETE à la fin qui prenait trop de temps.

Histoire de tester, je l'ai mis en commentaire et ça passe effectivement bien en moins d'une seconde. Ce n'est donc pas le merge des 10K lignes de @PRODUCTS qui pose problème.
(et pour être sûr, j'ai refait le test en retirant les variables locales).

Je vais donc me pencher sur l'optimisation de ce DELETE...

Est-ce que l'utilisation de l'opérateur EXISTS est plus performante que l'opérateur IN ???

[Kropernic]

Hello,

J'ai réécrit l'ordre DELETE pour utiliser l'oprérateur EXISTS à la place de IN et l'exécution est fulgurante.

Je ne savais pas qu'il y avait une si grande différence entre IN et EXISTS. Je pensais naïvement qu'en arrière plan, l'optimiseur réorganisait le tout pour produire la même requête dans un cas comme dans l'autre vu qu'au final, cela fait la même chose.

Je sais que je pourrais regarder aux plans d'exécution mais honnêtement, produire les variables tables pour tester dans ssms, ça va être chiant. Je pourrais facilement en produire une avec 5 produits mais ce ne serait rien de comparable avec mon test qui en contient 10000. Du coup, je pense que je vais être content que cela soit résolu et passer à autre chose.

Merci pour les suggestions !