Discussion :

[zoltix]

Bonsoir,
J’ai récupéré une application de base de données mais j’ai remarqué un gros de souci de lock. En fait, J’ai une table SEQUENCE dans lequel j’ai deux colonnes IDProc Char(10) et Séquence BigInt. Une procédure stocké insert une ligne pour la création d'une clef composite. La clef Composite c’est
IDProc+ Séquence
Example

Code :

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

1
2
3
4
5
6
7
8
IDProc	Séquence
AAAAA	1
AAAAA	2
BBBBB	1
BBBBB	2
AAAAA	3
BBBBB	3
CCCCC	1

Cette procédure stocké provoque de lock lors de la montée en charge c'est-à-dire lorsque plusieurs utilisateur essaye d’insérer en même temps ca provoque un lock et de plus la table grandit plus il y a des problèmes car
Example de memoire de la procédure

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
Create proc GenerateNextProcIDProc  @IDProc char(10), @Keys varhcar(20) out    
As
Declare @MaxValue char(10)
Begin trans
	Select  @MaxValue = max(Séquence)
From SEQUENCE with(rowlock,Xlock)
Where IDProc=@ IDProc	
If(@MaxValue is not null )
Begin 
Set  @Keys = @IDProc +cast(@MaxValue +1 as varchar)
Insert  SEQUENCE values(@IDProc, (@MaxValue +1)
	End
	Else 	
		Begin 
….
		End 
	Select @Keys
Commit

Comme vous pouvez le deviner ca provoque énormément de lock lorsque il y’a beaucoup d’utilisateurs et table grossi énormément donc ça devient de plus en plus long et plus en plus problématique.

Alors je recherche des idées pour réécrire cette procédure.
Si vous pouviez m’aider et me donner des conseils ce serait une aide précieuse.
Merci d’avance de votre aide.

[zoltix]

http://sqlpro.developpez.com/cours/clefs/

Je pense que je suis sur la bonne voix...mais j'aimerais avoir de bon conseil.
Merci

[elsuket]

Bonjour,

Outre le fait que le choix du type de données est catastrophique, puisque vous avez stocké des entiers dans des colonnes de type chaîne de caractère, on peut réécrire votre procédure stockée 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
24
25
26
27
ALTER PROCEDURE GenerateNextProcIDProc
	@IDProc char(10)
	, @KEYS varchar(20) OUT
AS
BEGIN
	SET NOCOUNT ON
 
	DECLARE @Sequence varchar(20)
 
	UPDATE	dbo.SEQUENCE
	SET	@Sequence = Sequence = CAST(Sequence AS bigint) + 1
	WHERE	IDProc = @IDProc
 
	IF @@ROWCOUNT = 0
	BEGIN
		INSERT	INTO dbo.SEQUENCE
		(
			IDProc
			, Keys
		)
		VALUES
		(
			@IDProc
			, @KEYS
		)
	END
END

Les verrous s'expliquent en partie par le fait que la fonction MAX() n'est pas requise du tout puisque de toute façon vous ne devriez avoir qu'une seule ligne avec la séquence générée (seulement la dernière valeur), et cela coûte très cher ...
Comme vous avez ajoutée les indicateurs de table ROWLOCK et XLOCK, le comportement devient contre-performant ...

On peut optimiser en utilisant les types de données correct, on peut redéfinir la table comme suit :

Code :

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

1
2
3
4
5
6
CREATE TABLE sequence
(
	procedure_object_id int NOT NULL CONSTRAINT PK_sequence PRIMARY KEY
	, sequence bigint NOT NULL
)
GO

On peut utiliser la fonction @@PROCID pour valuer la colonne procedure_object_id. Vous le passez en paramètre à l'appel la procédure stockée 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
26
CREATE PROCEDURE GenerateNextProcIDProc
	@procedure_object_id int
AS
BEGIN
	SET NOCOUNT ON
 
	DECLARE @sequence_value bigint
 
	UPDATE	dbo.sequence
	SET	@sequence = sequence = sequence + 1
	WHERE	procedure_object_id = @procedure_object_id
 
	IF @@ROWCOUNT = 0
	BEGIN
		INSERT	INTO dbo.sequence
		(
			procedure_object_id
			, sequence
		)
		VALUES
		(
			@procedure_object_id
			, 1
		)
	END
END

Enfin si vous êtes sous SQL Server 2012, vous pouvez utiliser SEQUENCE

@++

[zoltix]

Un grand merci pour votre réponse...... Maintenant j'ai le plein d'argument pour justifier ce changement....
Remarque: Je tiens à préciser que ce n'est pas moi qui ai écrit cela, je pense que j'aurais fait mieux ;-). Mais je cherchais surtout d'autre avis pour me conforter.

[elsuket]

Avec plaisir

Notez aussi que si vous êtes sous SQL Server 2008, vous pouvez utiliser l'instruction MERGE dans laquelle vous spécifieriez dans quel cas l'INSERT se produit et l'UPDATE de produit

A bientôt et n'oubliez pas de marquer votre discussion comme résolue (le bouton tout en bas de cette page, au milieu )

@++

[zoltix]

Je n'avais pas du pensé au "merge". Et je vais aussi essayer d'ajouter un output pour le paramètre de retour mais je ne suis pas sure car il faudra créer une table(variable ou temporaire) mais c'est peut être mieux de générer 2 fois.

Je répété un grand merci.