Discussion :

[Kropernic]

Bonjour,

Suite à la discussion sur l'identification relative du forum administration, je me décide à faire quelques tests histoire de pouvoir juger par moi-même.

Seulement, je bloque déjà au niveau du trigger chargé d'incrémenter cette colonne d'identification relative.
J'ai réussi en m'appuyant sur l'un ou l'autre article un créer un trigger qui fonctionne mais qui est itératif. Et je n'aime pas ça.
J'avais réussi à en créer un ensembliste mais qui ne fonctionnait qu'à moitié dans le sens que, si on voulait insérer un ensemble de lignes, il ne fonctionnait que pour des valeurs de GFT_ID différentes.

Sans plus tarder, un peu de code :
Le DDL de la table

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
/****** Object:  Table [dbo].[T_ACTIVATION_ACT]    Script Date: 10/04/2012 15:48:35 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[T_ACTIVATION_ACT](
    [GFT_ID] [int] NOT NULL, --clef primaire de la table parente
    [ACT_ID] [tinyint] NOT NULL, --colonne d'identification relative
    [DOS_ID] [int] NULL,
    [ACT_DATE] [datetime] NOT NULL,
    [ACT_VALUE] [decimal](6, 2) NOT NULL,
 CONSTRAINT [PK_T_ACTIVATION_ACT] PRIMARY KEY CLUSTERED 
(
    [GFT_ID] ASC,
    [ACT_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
ALTER TABLE [dbo].[T_ACTIVATION_ACT]  WITH CHECK ADD  CONSTRAINT [FK_T_ACTIVATION_ACT_TJ_GFT_DOS_JGD] FOREIGN KEY([GFT_ID], [DOS_ID])
REFERENCES [dbo].[TJ_GFT_DOS_JGD] ([GFT_ID], [DOS_ID])
GO
 
ALTER TABLE [dbo].[T_ACTIVATION_ACT] CHECK CONSTRAINT [FK_T_ACTIVATION_ACT_TJ_GFT_DOS_JGD]
GO

Et le DDL du trigger itératif :

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
/****** Object:  Trigger [dbo].[TR_RELATIVE_IDENTIFICATION_ACT]    Script Date: 10/04/2012 15:48:44 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TRIGGER [dbo].[TR_RELATIVE_IDENTIFICATION_ACT] ON [dbo].[T_ACTIVATION_ACT]
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
 
    SELECT 
            ROW_NUM = ROW_NUMBER() OVER(ORDER BY GFT_ID),
            *
    INTO    
            #INS
    FROM
            INSERTED;        
 
    DECLARE @ITER INT;
    DECLARE @COUNT INT;
 
    SELECT @ITER=MIN(ROW_NUM) FROM #INS;
    SELECT @COUNT=MAX(ROW_NUM) FROM #INS;
 
    WHILE @ITER <= @COUNT
    BEGIN
 
        INSERT INTO T_ACTIVATION_ACT(GFT_ID, ACT_ID, DOS_ID, ACT_DATE, ACT_VALUE)
        SELECT
                INS.GFT_ID,
                COALESCE(MAX(ACT.ACT_ID),0)+1,
                INS.DOS_ID,
                INS.ACT_DATE,
                INS.ACT_VALUE
        FROM
                T_ACTIVATION_ACT ACT
                    RIGHT OUTER JOIN #INS INS
                        ON    ACT.GFT_ID = INS.GFT_ID
        WHERE
                INS.ROW_NUM = @ITER
        GROUP BY
                INS.GFT_ID,
                INS.DOS_ID,
                INS.ACT_DATE,
                INS.ACT_VALUE;
 
        SET @ITER = @ITER+1;
    END
END
 
GO

J'ai encore du mal avec la logique ensembliste. C'est pourquoi je viens chercher votre aide.

Merci d'avance.

[SQLpro]

Utilisez une CTE d'UPDATE avec la fonction ROW_NUMBER() OVER(PARTITION ... ORDER BY ...) et ajouter la valeur du dernier incrément utilisé.
Pour ce faire vous devez impérativement monter au niveau d'isolation SERIALIZABLE, car il faut empêcher tout ajout de lignes au moment du calcul et de l'attribution des clefs, sinon, le risque de collision est grand...
Inconvénient de tout ceci, plus on monte le niveau d'isolation, et plus les attentes sont longues (donc moins de concurrence possible) et plus la possibilité de survenance d'un verrou mortel augmente. C'est pourquoi je n'utilise ni ne préconise jamais l'identification relative !

A +

[elsuket]

Bonjour,

Il est tard dans mon pays et, même après avoir relu le sujet original de Kropernic, je ne comprends pas pourquoi on ne peut pas écrire :

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
ALTER TRIGGER dbo.TR_RELATIVE_IDENTIFICATION_ACT
	ON dbo.T_ACTIVATION_ACT
INSTEAD OF INSERT
AS
BEGIN
	SET NOCOUNT ON
 
	INSERT	INTO dbo.T_ACTIVATION_ACT
	(
		GFT_ID
		, ACT_ID
		, DOS_ID
		, ACT_DATE
		, ACT_VALUE
	)
	SELECT		INS.GFT_ID
			, COALESCE(MAX(ACT.ACT_ID), 0) + 1
			, INS.DOS_ID
			, INS.ACT_DATE
			, INS.ACT_VALUE
	FROM		INSERTED AS INS
	INNER JOIN	dbo.T_ACTIVATION_ACT AS A
				ON INS.GFT_ID = A.GFT_ID
	GROUP BY	INS.GFT_ID
			, INS.DOS_ID
			, INS.ACT_DATE
			, INS.ACT_VALUE;
END

Est-ce que je dois réellement aller me coucher ?

En ce qui concerne le fait que SQL Server n'utilise que la colonne la plus à gauche d'une statistique pour effectuer une estimation de cardinalités, je me pose toujours la question de savoir pourquoi le vecteur statistique est collecté ...
En effet si on pose un index composite dont les colonnes sont toutes peu sélective, le vecteur, lui, peut le devenir.
De la même façon, j'ai observé certains cas où, pour une raison que je ne suis pas parvenu à établir, SQL Server choisira l'index dont l'ordre des colonnes est de la moins sélective à la plus sélective, alors même que l'index avec les colonnes dans l'ordre inverse n'est pas sélectionné. C'est donc bien que le vecteur est utilisé, mais quand ?

@++

[Kropernic]

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
ALTER TRIGGER TR_RELATIVE_IDENTIFICATION_ACT 
ON T_ACTIVATION_ACT 
INSTEAD OF INSERT 
AS 
BEGIN
     SET NOCOUNT ON;
     INSERT INTO T_ACTIVATION_ACT(GFT_ID, ACT_ID, ACT_DATE, ACT_VALUE)
     SELECT
             INS.GFT_ID,
             COALESCE(MAX(ACT.ACT_ID),0)+1,
             INS.ACT_DATE,
             INS.ACT_VALUE
     FROM
             T_ACTIVATION_ACT ACT
                 RIGHT OUTER JOIN INSERTED INS
                     ON    ACT.GFT_ID = INS.GFT_ID
     GROUP BY
             INS.GFT_ID,
             INS.ACT_DATE,
             INS.ACT_VALUE
END

Voilà le code du trigger que j'avais produit à la base. A part la jointure, je ne vois pas de différence.

Pourtant, cela ne fonctionnait pas.

Je faisais mes tests par paquets de 4 insertions.

Avec INNER JOIN, malgré le message me confirmant que 4 lignes avaient bien été insérées, je ne retrouvais rien dans ma table.

Avec RIGHT OUTER JOIN, je retrouvais bien mes lignes mais cela ne fonctionnait que pour des valeurs de GFT_ID différentes. Sans quoi, il tentait d'insérer plusieurs fois le même couple (GFT_ID, ACT_ID) et évidemment, vu qu'il s'agit de la clef primaire, ça ne va pas.

Etant donné qu'il s'agit d'une application qui sera utilisée par relativement peu d'utilisateur et les tables ayant une identification relative ne seront utilisées que par un seul service (composés de 3 personnes), cela ne conviendrait-il tout de même pas malgré les réticences exposés par sqlpro ?

EDIT : La colonne DOS_ID n'est apparue qu'après lorsque je me suis aperçu d'une erreur sur le mcd.

[SQLpro]

Il es difficile de prévoir l'avenir... Aujourd'hui 3 personnes. Demain peut être zéro comme 100 !
Mettez au moins le niveau d’isolation SERIALIZABLE et redescendez en fin de traitement à READ COMMITTED.

A +

[Kropernic]

Il es difficile de prévoir l'avenir... Aujourd'hui 3 personnes. Demain peut être zéro comme 100 !

C'est certain mais n'étant pas dans une boite de consultance, nous développons en interne pour notre entreprise. Ce qui veut dire qu'on connait le public cible de nos applications. Et je vous garantis que la tendance est plus à la diminition du personnel que l'inverse

Mettez au moins le niveau d’isolation SERIALIZABLE et redescendez en fin de traitement à READ COMMITTED.

Je n'ai jamais fait cela jusqu'ici, je vais tacher de voir ce qui ce dit sur le sujet. J'étais déjà tombé sur un de vos articles parlant du niveau d'isolation des transactions mais je dois bien avouer que ça m'étais passé un peu par dessus la tête...

[Kropernic]

Bon, je pense avoir pigé le truc pour les niveaux d'isolation.

Par contre, je bloque toujours sur la CTE d'UPDATE. J'ai bien trouvé un exemple sur le net mais je ne vois pas trop comment l'appliqué dans mon cas.