[MSSQL] Problème de deadlock

Version imprimable

Voir 40 message(s) de cette discussion en une page

24/06/2011, 11h38
elsuket

Citation:

ElSuket, j'ai essayé votre code et malheureusement cela ne fonctionne pas.

Héhé d'accord, mais quel est le problème ?
Est-ce que vos valeurs de chaîne se terminent bien par ...

AM'.
AUTO'.
MANU'.
HS'.
S'.

... c'est à dire apostrophe et point compris ?

En outre :

Code:

OR (DATEDIFF(dd, TimeStmp, GETDATE()) > 2558)

C'est pas beau, parce que SQL Server ne maintient pas de statistiques sur DATEDIFF(dd, TimeStmp, GETDATE()), mais sur TimeStmp.

En écrivant votre requête de la sorte, vous ne permettez pas à SQL Server d'estimer combien de lignes vérifient le prédicat.

En revanche si vous écrivez :

Code:

OR TimeStmp > DATEADD(year, -7, GETDATE())

Là c'est mieux.

Le problème demeure encore puisque :

- Vous êtes dans un trigger
- le prédicat est à base de OR

@++ ;)

Citation:

Héhé d'accord, mais quel est le problème ?
Est-ce que vos valeurs de chaîne se terminent bien par ...

AM'.
AUTO'.
MANU'.
HS'.
S'.

... c'est à dire apostrophe et point compris ?

Mes chaînes se terminent bien par ces caractères.

Citation:

En outre :
Code :
Sélectionner tout - Visualiser dans une fenêtre à part

OR (DATEDIFF(dd, TimeStmp, GETDATE()) > 2558)

C'est pas beau, parce que SQL Server ne maintient pas de statistiques sur DATEDIFF(dd, TimeStmp, GETDATE()), mais sur TimeStmp.

En écrivant votre requête de la sorte, vous ne permettez pas à SQL Server d'estimer combien de lignes vérifient le prédicat.

En revanche si vous écrivez :
Code :
Sélectionner tout - Visualiser dans une fenêtre à part

OR TimeStmp > DATEADD(year, -7, GETDATE())

Là c'est mieux.

D'acord mais ai-je le même problème que les DATEDIFF pour travailler sur les années ? Je m'explique :

un DATEDIFF en années entre 31/12/2011 et 01/01/2004 renverrait 7 ans alors qu'en vérité cela fait 6 ans et 1 jours.

Sinon j'ai pensé à fonctionner autrement :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 Create TRIGGER Filtre_Donnes_entrantes ON Action_Ope INSTEAD of INSERT as IF NOT (messagetext like '%AM''.'or messagetext like '%AUTO''.') INSERT INTO Audit_Trail (TimeStmp, MessageText, Location, UserID, UserFullName) SELECT TimeStmp, MessageText, Location, UserID, UserFullName FROM Action_Ope GO
Enfaite les données sont insérées dans une table temporaire qui elle renvoi ces données dans une table principale donc je pense que ça peut grandement limiter les deadlocks?

Cependant j'ai ce message d'erreur sur la ligne du IF NOT :
Msg 207, Level 16, State 1, Procedure Filtre_Donnes_entrantes, Line 9
Nom de colonne non valide*: 'messagetext'.
Msg 207, Level 16, State 1, Procedure Filtre_Donnes_entrantes, Line 9
Nom de colonne non valide*: 'messagetext'.

alors que ma colonne est valide je ne comprends pas.

Merci pour votre aide.

Bonne journée.

Citation:

C'est à dire que je vais créer une table contenant mes mots clés inutiles et l'autre va vérifier si les enregistrements ne contiennent pas ceux ci ? Pouvez vous me guider dans cette démarche car cela dépasse le peu de connaissances en sql actuelles.

Oui, il vous suffit de créer une table de mots "noirs", que j'appelle mot_noir dans l'exemple qui 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 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 CREATE TRIGGER TR_IOF_I_Action_OPE ON Action_Ope INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON INSERT INTO dbo.Action_OPE ( RecordID , ParentID , SequenceNumber , Chaintime , TimeStmp , MessageText , Audience , Severity , Verbosity , Location , Provider , UserID , UserFullName ) SELECT DISTINCT RecordID , ParentID , SequenceNumber , Chaintime , TimeStmp , MessageText , Audience , Severity , Verbosity , Location , Provider , UserID , UserFullName FROM inserted AS I CROSS JOIN dbo.mot_noir AS MN WHERE I.messagetext NOT LIKE '%' + MN.mot_noir + '%' END
Ou encore :
Code:

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 CREATE TRIGGER TR_IOF_I_Action_OPE ON Action_Ope INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON INSERT INTO dbo.Action_OPE ( RecordID , ParentID , SequenceNumber , Chaintime , TimeStmp , MessageText , Audience , Severity , Verbosity , Location , Provider , UserID , UserFullName ) SELECT RecordID , ParentID , SequenceNumber , Chaintime , TimeStmp , MessageText , Audience , Severity , Verbosity , Location , Provider , UserID , UserFullName FROM inserted AS I CROSS APPLY ( SELECT CASE WHEN SUM(exclude_flag) > 0 THEN 1 ELSE 0 END AS exclude_flag FROM ( SELECT CASE WHEN I.MessageText LIKE '%' + M.libelle + '%' THEN 1 ELSE 0 END AS exclude_flag FROM dbo.mot_noir AS M ) AS S ) AS E WHERE E.exclude = 0 END
Qui me semble mieux.

Citation:

Mes chaînes se terminent bien par ces caractères.

D'accord, mais cela ne nous dit pas le problème que vous rencontrez.
Donnez des chaînes exemples, et le message d'erreur ou le problème auquel vous êtes exposé.

Citation:

un DATEDIFF en années entre 31/12/2011 et 01/01/2004 renverrait 7 ans alors qu'en vérité cela fait 6 ans et 1 jours.

Non, c'est bien le cas : entre le 1er Janvier 2004 et le 31 décembre 2011 il y a 7 ans ... et 364 jours.
notez que les valeurs de type datetime sont à minuit ;)
D'autre part rien ne vous empêche d'écrire :

Code:

DATEADD(day, -2558, GETDATE())

Citation:

Sinon j'ai pensé à fonctionner autrement :

L'erreur qui suit s'explique tout à fait, puisque SQL Server ne sait pas de quelle table provient la colonne MessageText : vous ne l'indiquez pas.
Cela vient du fait que vous avez considéré que SQL Server traite les lignes une par une, ce qui ne doit jamais être le cas dans votre code, et qui n'est pas le cas du trigger que je vous ai proposé.

@++ ;)

Après plusieurs tests, ça ne marche pas. Je vais décrire ma procédure pour voir si je fais quelque chose de mal :

I - Je crée une table "mot_noir" contenant la colonne "mot". J'ajoute des enregistrements : Set, Display,..

II - Je crée une table "Table_RECP" qui va recevoir l'enregistrement de la supervision.

III - Je créer un trigger associeé à "Table_RECP"

Donc là vos deux solutions la première :
Code:

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 CREATE TRIGGER TR_IOF_I_Table_RECP ON Table_RECP INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON INSERT INTO dbo.Table_RECP ( RecordID , ParentID , SequenceNumber , Chaintime , TimeStmp , MessageText , Audience , Severity , Verbosity , Location , Provider , UserID , UserFullName ) SELECT DISTINCT RecordID , ParentID , SequenceNumber , Chaintime , TimeStmp , MessageText , Audience , Severity , Verbosity , Location , Provider , UserID , UserFullName FROM inserted AS I CROSS JOIN dbo.mot_noir AS MN WHERE I.messagetext NOT LIKE '%' + MN.mot + '%' END
La supervision envoie les enregistrements et ceux contenants Set, Display,... sont toujours présents.

La deuxième :
Code:

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 TRIGGER TR_IOF_I_Table_RECP ON Table_RECP INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON INSERT INTO dbo.Table_RECP ( RecordID , ParentID , SequenceNumber , Chaintime , TimeStmp , MessageText , Audience , Severity , Verbosity , Location , Provider , UserID , UserFullName ) SELECT RecordID , ParentID , SequenceNumber , Chaintime , TimeStmp , MessageText , Audience , Severity , Verbosity , Location , Provider , UserID , UserFullName FROM inserted AS I CROSS APPLY ( SELECT CASE WHEN SUM(exclude_flag) > 0 THEN 1 ELSE 0 END AS exclude_flag FROM ( SELECT CASE WHEN I.MessageText LIKE '%' + M.mot + '%' THEN 1 ELSE 0 END AS exclude_flag FROM dbo.mot_noir AS M ) AS S ) AS E WHERE E.exclude = 0 END
J'ai comme message d'erreur :"Exclude n'est pas un nom de colonne valide"

En tout cas merci beaucoup de votre patience car sans vous je serais dans une sacré galère.

27/06/2011, 17h20
elsuket

Citation:

En tout cas merci beaucoup de votre patience car sans vous je serais dans une sacré galère.

Aucun problème, on va tordre le coup à ce problème ;)

C'est vraiment bizarre parce que j'ai testé des deux triggers ...
Comme j'ai modifié les libellés de colonne avant de publier :

Code:

WHERE E.exclude = 0

Il faut le changer en :

Code:

WHERE E.exclude_flag = 0

Je vais refaire un test pour le reste.

@++ ;)

Bonjour,

Pour la première solution, j'aurai plutôt vu :

Code:

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
 
CREATE TRIGGER TR_IOF_I_Table_RECP
	ON Table_RECP
INSTEAD OF INSERT
AS
BEGIN
	SET NOCOUNT ON
 
	INSERT	INTO dbo.Table_RECP
	(
		RecordID
		, ParentID
		, SequenceNumber
		, Chaintime
		, TimeStmp
		, MessageText
		, Audience
		, Severity
		, Verbosity
		, Location
		, Provider
		, UserID
		, UserFullName
	)
	SELECT		DISTINCT RecordID
			, ParentID
			, SequenceNumber
			, Chaintime
			, TimeStmp
			, MessageText
			, Audience
			, Severity
			, Verbosity
			, Location
			, Provider
			, UserID
			, UserFullName
	FROM		inserted AS I
	WHERE NOT EXISTS (
		SELECT *
		FROM dbo.mot_noir AS MN
		WHERE		I.messagetext LIKE '%' + MN.mot + '%' 
	)

Bon, voici mon test, réalisé sous SQL Server 2008 :

Code:

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
SET NOCOUNT ON
GO
 
CREATE TABLE mot_noir
(
	id_mot_noir int IDENTITY NOT NULL CONSTRAINT XPKtest_mot_noir PRIMARY KEY
	, mot_noir varchar(32) NOT NULL CONSTRAINT UQtest_mot_noir UNIQUE
)
GO
 
INSERT	INTO dbo.mot_noir (mot_noir)
VALUES	('server'), ('computer'), ('machine')
GO
 
CREATE TABLE resultat
(
	id_resultat int IDENTITY NOT NULL CONSTRAINT XPKresultat PRIMARY KEY
	, sentence varchar(100) NOT NULL
)
GO
 
CREATE TRIGGER TR_IOF_I_resultat
	ON dbo.resultat
INSTEAD OF INSERT
AS
BEGIN
	SET NOCOUNT ON
 
	INSERT		INTO dbo.resultat (sentence)
	SELECT		I.sentence
	FROM		inserted AS I
	CROSS APPLY	(
				SELECT	CASE WHEN SUM(exclude_flag) > 0 THEN 1 ELSE 0 END AS exclude_flag
				FROM	(
						SELECT	CASE WHEN I.sentence LIKE '%' + M.mot_noir + '%' THEN 1 ELSE 0 END AS exclude_flag
						FROM	dbo.mot_noir AS M
					) AS S
			) AS E
	WHERE		E.exclude_flag = 0
END
GO
 
INSERT	dbo.resultat (sentence) VALUES ('The server is burning !')
INSERT	dbo.resultat (sentence) VALUES ('le serveur prend feu !')
INSERT	dbo.resultat (sentence) VALUES ('This computer will soon pass through the window !')
INSERT	dbo.resultat (sentence) VALUES ('Cet ordinateur ne va pas tarder à passer à travers la fenêtre !')
 
SELECT	*
FROM	dbo.resultat

Affiche :

Code:

1
2
3
id_resultat	sentence
1	le serveur prend feu !
2	Cet ordinateur ne va pas tarder à passer à travers la fenêtre !

Donc pour moi c'est bon. Désolé pour l'erreur sur le libellé de colonne.

Le premier trigger ne fait pas ce que vous recherchez car il filtre les phrases qui ne contiennent pas le mot noir, mais garde les autres ... Désolé encore pour cette bourde.

En conclusion, vous pouvez corriger le second trigger avec la correction que je vous ai donné dans le post n°25.

@++ ;)

28/06/2011, 08h38
pitipilot

C'est bon ça marche parfaitement :D. C'est vraiment très très efficace.

Sinon pour supprimer les enregistrements de plus de 7 ans comment je peux faire pour effectuer ça en fin de journée par exemple ?

En tout cas merci énormément car là finis les foutus deadlocks
28/06/2011, 09h39
WOLO Laurent

Est ce que ca ne serait pas plus interressant d'ouvrir un nouveau topic, c'est plus profitable. N'est ce pas ?
Sinon, il est recommandé de faire une tâche planifiée via SQL Agent.
28/06/2011, 09h54
mikedavem

En sus de ce qu'a dit WOLO Laurent, prévoyez une maintenance de vos index et statistiques après cette suppression massive.

++

Voir 40 message(s) de cette discussion en une page