Problème de timeout suite à l'ajout d'une contrainte check

**Kropernic** · 12/01/2016, 11h14

Hello,

Je me tourne vers vous car je ne sais pas trop quoi faire dans le cas présent. J'imagine bien que l'ajout d'un index devrait m'aider mais j'ai toujours beaucoup de mal à savoir le(s)quel(s) mettre en place...

J'ai donc la table suivante :

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
CREATE TABLE [S_PROMO].[T_PRODUCT_LIST_OWN_PLO](
    [DTO_ID] [int] NOT NULL,
    [PLO_BARCODE] [char](13) NOT NULL,
    [PLO_INCLUSIVE] [bit] NOT NULL,
    [PLO_COLOR] [varchar](50) NULL,
    [PLO_SIZE] [varchar](50) NULL,
    [PLO_DESC] [varchar](50) NULL,
    [PLO_PRICE] [varchar](50) NULL,
    [PLO_CREATED_ON] [datetime] NOT NULL CONSTRAINT [DF__T_PRODUCT__PLO_C__5027A6DA]  DEFAULT (getdate()),
    [PLO_CREATED_BY] [varchar](100) NOT NULL CONSTRAINT [DF__T_PRODUCT__PLO_C__511BCB13]  DEFAULT (suser_sname()),
    [PLO_MODIFIED_ON] [datetime] NULL,
    [PLO_MODIFIED_BY] [varchar](100) NULL,
    [PLO_NSA] [varchar](50) NULL,
    [PLO_REF] [varchar](50) NULL,
 CONSTRAINT [PK_T_PRODUCT_LIST_OWN_PLO] PRIMARY KEY CLUSTERED 
(
    [DTO_ID] ASC,
    [PLO_BARCODE] ASC
)) ON [PRIMARY]
GO
 
ALTER TABLE [S_PROMO].[T_PRODUCT_LIST_OWN_PLO]  WITH CHECK ADD  CONSTRAINT [FK_PLO_TO_DTO] FOREIGN KEY([DTO_ID])
REFERENCES [S_PROMO].[T_DETAIL_OWN_DTO] ([DTO_ID])
ON DELETE CASCADE
GO

Sur laquelle j'ai ajouté la contrainte suivante (le code de la fonction suit celui de la contrainte) :

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
ALTER TABLE [S_PROMO].[T_PRODUCT_LIST_OWN_PLO]  WITH NOCHECK ADD  CONSTRAINT [CK_DOUBLE_PROMO] CHECK  (([S_PROMO].[UF_CHECK_DOUBLE_EAN_OWN]([PLO_BARCODE],[DTO_ID])=(0)))
GO
 
CREATE FUNCTION [S_PROMO].[UF_CHECK_DOUBLE_EAN_OWN] (@BARCODE CHAR(13), @DTO_ID INT) RETURNS BIT
AS
BEGIN
    DECLARE @RESULT BIT;
    DECLARE @COUNT    INT;
    DECLARE @PRM_ID INT;
 
    DECLARE @DTO_ID_BIS INT = @DTO_ID;
    DECLARE @BARCODE_BIS CHAR(13) = @BARCODE;
 
    SELECT    @PRM_ID = PRM_ID
    FROM    S_PROMO.T_DETAIL_OWN_DTO
    WHERE    DTO_ID = @DTO_ID_BIS;
 
    SELECT    @COUNT = COUNT(*)
    FROM    S_PROMO.T_PRODUCT_LIST_OWN_PLO PLO
                INNER JOIN S_PROMO.T_DETAIL_OWN_DTO DTO
                    ON    PLO.DTO_ID = DTO.DTO_ID
    WHERE    DTO.PRM_ID = @PRM_ID
        AND PLO.PLO_BARCODE = @BARCODE_BIS;
 
    IF @COUNT < 2
        SET @RESULT = 0
    ELSE    
        SET @RESULT = 1
 
    RETURN @RESULT;
END

(comme vous le voyez, j'ai déjà suspecté un problème de parameters sniffing, d'où la duplication des paramètres de la fonction en variables locales mais cela n'a pas résolu le problème, ce n'est donc pas ça je pense)
Le but étant d'empêcher, qu'au sein d'une même promo, un article soit repris deux fois (et recevrait alors 2 fois la promo --> pas bon pour le business

)

La table S_PROMO.T_PRODUCT_LIST_OWN_PLO compte 213780 lignes. Vraiment pas grand chose donc.

Le problème se produit lorsque, via l'interface applicatif (.NET), l'utilisateur veut copier une promo. Cette copie impacte bien sûr tout un tas de table mais j'ai déjà pu, via l'outil "Activity monitor" de SSMS, circonscrire le problème à cette contrainte (d'ailleurs si je retire la contrainte, la copie de promo se passe bien). On parle, dans ce cas-ci, de 9531 lignes à insérer dans cette table. Cela ne me semble pas du tout gargantuesque (c'est l'inverse même)...

Pour diable alors cette contrainte fait-elle exploser le timeout ?

Si vous avez besoin d'autres codes DDL pour pouvoir vous prononcer, n'hésitez pas à demander.

Merci d'avance.

**aieeeuuuuu** · 12/01/2016, 18h09

Bonjour,

Je n'ai pas le temps de détailler le problème, mais déjà quelque remarques / questions :

Que contient la table T_PRODUCT_LIST_OWN_PLO ?

un index (PLO_BARCODE, DTO_ID) pourrait être une bonne chose (l'ordre des colonnes de la clef primaire est inversé pour l'index cluster).

Pourquoi faire deux requêtes distinctes dans votre fonction ? a priori, une seule suffit.

une vue indexée pourrait aussi être une solution.

**SQLpro** · 12/01/2016, 19h26

Envoyé par Kropernic

Hello,

Je me tourne vers vous car je ne sais pas trop quoi faire dans le cas présent. J'imagine bien que l'ajout d'un index devrait m'aider mais j'ai toujours beaucoup de mal à savoir le(s)quel(s) mettre en place...

J'ai donc la table suivante :

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
CREATE TABLE [S_PROMO].[T_PRODUCT_LIST_OWN_PLO](
    [DTO_ID] [int] NOT NULL,
    [PLO_BARCODE] [char](13) NOT NULL,
    [PLO_INCLUSIVE] [bit] NOT NULL,
    [PLO_COLOR] [varchar](50) NULL,
    [PLO_SIZE] [varchar](50) NULL,
    [PLO_DESC] [varchar](50) NULL,
    [PLO_PRICE] [varchar](50) NULL,
    [PLO_CREATED_ON] [datetime] NOT NULL CONSTRAINT [DF__T_PRODUCT__PLO_C__5027A6DA]  DEFAULT (getdate()),
    [PLO_CREATED_BY] [varchar](100) NOT NULL CONSTRAINT [DF__T_PRODUCT__PLO_C__511BCB13]  DEFAULT (suser_sname()),
    [PLO_MODIFIED_ON] [datetime] NULL,
    [PLO_MODIFIED_BY] [varchar](100) NULL,
    [PLO_NSA] [varchar](50) NULL,
    [PLO_REF] [varchar](50) NULL,
 CONSTRAINT [PK_T_PRODUCT_LIST_OWN_PLO] PRIMARY KEY CLUSTERED 
(
    [DTO_ID] ASC,
    [PLO_BARCODE] ASC
)) ON [PRIMARY]
GO
 
ALTER TABLE [S_PROMO].[T_PRODUCT_LIST_OWN_PLO]  WITH CHECK ADD  CONSTRAINT [FK_PLO_TO_DTO] FOREIGN KEY([DTO_ID])
REFERENCES [S_PROMO].[T_DETAIL_OWN_DTO] ([DTO_ID])
ON DELETE CASCADE
GO

Sur laquelle j'ai ajouté la contrainte suivante (le code de la fonction suit celui de la contrainte) :

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
ALTER TABLE [S_PROMO].[T_PRODUCT_LIST_OWN_PLO]  WITH NOCHECK ADD  CONSTRAINT [CK_DOUBLE_PROMO] CHECK  (([S_PROMO].[UF_CHECK_DOUBLE_EAN_OWN]([PLO_BARCODE],[DTO_ID])=(0)))
GO
 
CREATE FUNCTION [S_PROMO].[UF_CHECK_DOUBLE_EAN_OWN] (@BARCODE CHAR(13), @DTO_ID INT) RETURNS BIT
AS
BEGIN
    DECLARE @RESULT BIT;
    DECLARE @COUNT    INT;
    DECLARE @PRM_ID INT;
 
    DECLARE @DTO_ID_BIS INT = @DTO_ID;
    DECLARE @BARCODE_BIS CHAR(13) = @BARCODE;
 
    SELECT    @PRM_ID = PRM_ID
    FROM    S_PROMO.T_DETAIL_OWN_DTO
    WHERE    DTO_ID = @DTO_ID_BIS;
 
    SELECT    @COUNT = COUNT(*)
    FROM    S_PROMO.T_PRODUCT_LIST_OWN_PLO PLO
                INNER JOIN S_PROMO.T_DETAIL_OWN_DTO DTO
                    ON    PLO.DTO_ID = DTO.DTO_ID
    WHERE    DTO.PRM_ID = @PRM_ID
        AND PLO.PLO_BARCODE = @BARCODE_BIS;
 
    IF @COUNT < 2
        SET @RESULT = 0
    ELSE    
        SET @RESULT = 1
 
    RETURN @RESULT;
END

(comme vous le voyez, j'ai déjà suspecté un problème de parameters sniffing, d'où la duplication des paramètres de la fonction en variables locales mais cela n'a pas résolu le problème, ce n'est donc pas ça je pense)
Le but étant d'empêcher, qu'au sein d'une même promo, un article soit repris deux fois (et recevrait alors 2 fois la promo --> pas bon pour le business

)

La table S_PROMO.T_PRODUCT_LIST_OWN_PLO compte 213780 lignes. Vraiment pas grand chose donc.

Le problème se produit lorsque, via l'interface applicatif (.NET), l'utilisateur veut copier une promo. Cette copie impacte bien sûr tout un tas de table mais j'ai déjà pu, via l'outil "Activity monitor" de SSMS, circonscrire le problème à cette contrainte (d'ailleurs si je retire la contrainte, la copie de promo se passe bien). On parle, dans ce cas-ci, de 9531 lignes à insérer dans cette table. Cela ne me semble pas du tout gargantuesque (c'est l'inverse même)...

Pour diable alors cette contrainte fait-elle exploser le timeout ?

Si vous avez besoin d'autres codes DDL pour pouvoir vous prononcer, n'hésitez pas à demander.

Merci d'avance.

Attention, dans les UDF au verrouillage induit par la transaction implicite de la mise à jour.... Cette UDF ne voit pas forcément les données en cours de MAJ et peut être bloquée par les verrous posés par la transaction....

Décrivez votre but et donnez le détail DDL de vos table avec un jeu d'essai

A +

**Kropernic** · 13/01/2016, 08h27

Envoyé par aieeeuuuuu

Que contient la table T_PRODUCT_LIST_OWN_PLO ?

Cette table contient une liste de produits (articles)

Envoyé par aieeeuuuuu

un index (PLO_BARCODE, DTO_ID) pourrait être une bonne chose (l'ordre des colonnes de la clef primaire est inversé pour l'index cluster).

J'aimerais comprendre. Qu'est-ce qui vous fait dire que cet index serait utile ?

Envoyé par aieeeuuuuu

Pourquoi faire deux requêtes distinctes dans votre fonction ? a priori, une seule suffit.

Car via les paramètres, je n'ai pas l'identifiant de la promo et j'en ai besoin pour filtrer correctement la requête faisant le compte.

Envoyé par aieeeuuuuu

une vue indexée pourrait aussi être une solution.

Ah tiens pourquoi pas !

N.B. : Je vois que kankuru me dit qu'il manque quelques indexes pour cette DB et ce schéma dont 5 sur la table DTO et 1 sur la table PLO (mais pas celui que vous mentionnez ^^). Je vais commencer par les ajouter pour voir.

**Kropernic** · 13/01/2016, 08h40

Envoyé par SQLpro

Attention, dans les UDF au verrouillage induit par la transaction implicite de la mise à jour.... Cette UDF ne voit pas forcément les données en cours de MAJ et peut être bloquée par les verrous posés par la transaction....

0_0 Comment alors, dans ce genre de cas, fait-on pour faire respecter une contrainte nécessitant une fonction ?

Envoyé par SQLpro

Décrivez votre but et donnez le détail DDL de vos table avec un jeu d'essai

Le but est que, pour une même promo (donc un même PRM_ID dans la table DTO), il n'y a pas 2 lignes dans la table PLO avec le même barcode.

Pour le DDL, le voici ci-dessous. Pour le jeu d'essai, je ne peux pas fournir de données... C'est considéré comme confidentiel (même si je ne vois vraiment pas pourquoi...)

DDL :

Il y a la table T_PROMO_PRM qui contient la liste des promotions :

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
CREATE TABLE [S_PROMO].[T_PROMO_PRM](
    [PRM_ID] [int] IDENTITY(-2147483648,1) NOT NULL,
    [PRM_STARTDATE] [date] NOT NULL,
    [PRM_ENDDATE] [date] NOT NULL,
    [PRM_STARTTIME] [time](0) NOT NULL,
    [PRM_ENDTIME] [time](0) NOT NULL,
    [PRM_DESC] [varchar](50) NOT NULL,
    [PRM_CREATED_ON] [datetime] NOT NULL CONSTRAINT [DF__T_PROMO_P__PRM_C__03F163A3]  DEFAULT (getdate()),
    [PRM_CREATED_BY] [varchar](100) NOT NULL CONSTRAINT [DF__T_PROMO_P__PRM_C__04E587DC]  DEFAULT (suser_sname()),
    [PRM_MODIFIED_ON] [datetime] NULL,
    [PRM_MODIFIED_BY] [varchar](100) NULL,
    [PRT_ID] [tinyint] NOT NULL,
 CONSTRAINT [PK_T_PROMO_PRM] PRIMARY KEY CLUSTERED 
(
    [PRM_ID] ASC
)) ON [PRIMARY]
GO
 
ALTER TABLE [S_PROMO].[T_PROMO_PRM]  WITH CHECK ADD  CONSTRAINT [FK_PRM_TO_PRT] FOREIGN KEY([PRT_ID])
REFERENCES [S_PROMO].[T_PROMO_TYPE_PRT] ([PRT_ID])

Il y a la table T_DETAIL_OWN_DTO qui contient les tuples marque;rayon (BRA_ID;DEP_ID) qui sont concernés par une promo :

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
CREATE TABLE [S_PROMO].[T_DETAIL_OWN_DTO](
    [DTO_ID] [int] IDENTITY(-2147483648,1) NOT NULL,
    [PRM_ID] [int] NOT NULL,
    [DEP_ID] [smallint] NOT NULL,
    [BRA_ID] [int] NOT NULL,
    [DTO_PERCENT] [decimal](4, 2) NOT NULL,
    [DTO_DESC] [varchar](200) NOT NULL,
    [DTO_NOTE] [varchar](200) NULL,
    [BA_ID] [tinyint] NOT NULL,
    [DTO_ENCODED] [bit] NOT NULL CONSTRAINT [DF_T_DETAIL_OWN_DTO_DTO_ENCODED]  DEFAULT ((0)),
    [PCT_ID] [tinyint] NOT NULL,
    [DTO_CREATED_ON] [datetime] NOT NULL CONSTRAINT [DF__T_DETAIL___DTO_C__44B5F42E]  DEFAULT (getdate()),
    [DTO_CREATED_BY] [varchar](100) NOT NULL CONSTRAINT [DF__T_DETAIL___DTO_C__45AA1867]  DEFAULT (suser_sname()),
    [DTO_MODIFIED_ON] [datetime] NULL,
    [DTO_MODIFIED_BY] [varchar](100) NULL,
 CONSTRAINT [PK_T_DETAIL_OWN_DTO] PRIMARY KEY CLUSTERED 
(
    [DTO_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [AK_T_DETAIL_OWN_DTO] UNIQUE NONCLUSTERED 
(
    [PRM_ID] ASC,
    [DEP_ID] ASC,
    [BRA_ID] ASC,
    [DTO_PERCENT] ASC,
    [PCT_ID] ASC
)) ON [PRIMARY]
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_OWN_DTO]  WITH CHECK ADD  CONSTRAINT [FK_DTO_TO_BA] FOREIGN KEY([BA_ID])
REFERENCES [dbo].[T_BUREAU_ACHAT_BA] ([BA_ID])
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_OWN_DTO]  WITH CHECK ADD  CONSTRAINT [FK_DTO_TO_BRA] FOREIGN KEY([BRA_ID])
REFERENCES [dbo].[T_BRAND_BRA] ([BRA_ID])
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_OWN_DTO]  WITH CHECK ADD  CONSTRAINT [FK_DTO_TO_DEP] FOREIGN KEY([DEP_ID])
REFERENCES [dbo].[T_DEPARTEMENT_DEP] ([DEP_ID])
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_OWN_DTO]  WITH CHECK ADD  CONSTRAINT [FK_DTO_TO_PCT] FOREIGN KEY([PCT_ID])
REFERENCES [S_PROMO].[T_PERCENT_TYPE_PCT] ([PCT_ID])
GO
 
ALTER TABLE [S_PROMO].[T_DETAIL_OWN_DTO]  WITH CHECK ADD  CONSTRAINT [FK_DTO_TO_PRM] FOREIGN KEY([PRM_ID])
REFERENCES [S_PROMO].[T_PROMO_PRM] ([PRM_ID])

Je ne détaille pas les tables "satellites" mais si c'est nécessaire, suffit de demander.

Et il y a donc la table T_PRODUCT_LIST_OWN_PLO qui contient les articles d'un tuple marque;rayon qui sont concernés par une promo dans le cas où tous ne sont pas concernés :

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
CREATE TABLE [S_PROMO].[T_PRODUCT_LIST_OWN_PLO](
    [DTO_ID] [int] NOT NULL,
    [PLO_BARCODE] [char](13) NOT NULL,
    [PLO_INCLUSIVE] [bit] NOT NULL,
    [PLO_COLOR] [varchar](50) NULL,
    [PLO_SIZE] [varchar](50) NULL,
    [PLO_DESC] [varchar](50) NULL,
    [PLO_PRICE] [varchar](50) NULL,
    [PLO_CREATED_ON] [datetime] NOT NULL CONSTRAINT [DF__T_PRODUCT__PLO_C__5027A6DA]  DEFAULT (getdate()),
    [PLO_CREATED_BY] [varchar](100) NOT NULL CONSTRAINT [DF__T_PRODUCT__PLO_C__511BCB13]  DEFAULT (suser_sname()),
    [PLO_MODIFIED_ON] [datetime] NULL,
    [PLO_MODIFIED_BY] [varchar](100) NULL,
    [PLO_NSA] [varchar](50) NULL,
    [PLO_REF] [varchar](50) NULL,
 CONSTRAINT [PK_T_PRODUCT_LIST_OWN_PLO] PRIMARY KEY CLUSTERED 
(
    [DTO_ID] ASC,
    [PLO_BARCODE] ASC
)) ON [PRIMARY]
GO
 
ALTER TABLE [S_PROMO].[T_PRODUCT_LIST_OWN_PLO]  WITH CHECK ADD  CONSTRAINT [FK_PLO_TO_DTO] FOREIGN KEY([DTO_ID])
REFERENCES [S_PROMO].[T_DETAIL_OWN_DTO] ([DTO_ID])
ON DELETE CASCADE

Ces articles peuvent être en "inclusion" (PLO_INCLUSIVE = 1) pour dire qu'ils bénéficient de la promo ou en "exclusion" (PLO_INCLUSIVE = 0) pour dire le contraire.

Voilà, j'espère qu'il y a tout. Et comme déjà dit, si ce n'est pas suffisant, il n'y a qu'à demander.

Sur ce, je m'en vais créer les quelques indexes manquants signalés par kankuru.

**Kropernic** · 13/01/2016, 10h30

Envoyé par Kropernic

N.B. : Je vois que kankuru me dit qu'il manque quelques indexes pour cette DB et ce schéma dont 5 sur la table DTO et 1 sur la table PLO (mais pas celui que vous mentionnez ^^). Je vais commencer par les ajouter pour voir.

Après avoir ajouté 1 index sur la table DTO (les autres étaient redondant) et celui sur la table PLO, mon problème semble résolu.

Je passe donc le status de cette discussion sur résolu.

Merci à vous deux d'avoir pris le temps d'y répondre.

Problème de timeout suite à l'ajout d'une contrainte check

Développement SQL Server

Discussions similaires

Partager

Partager