optimiser une procédure stockée

**ed222** · 14/06/2010, 15h18

Bonjour,

j'ai écrit une procédure stockée qui prend en paramètres nom, prénom, tel, métier.
je dispose de 3 tables : liste (des personnes), métiers et personne_métiers (sui fait le lien entre 1 personne et 1 ou plieurs métiers).
la procédure ne porte que sur le test suivant le nom.

Comme vous pourrez le constater, elle est très longue au regard de ce qu'elle est censée faire (les tables ont très peu de ligne heureusement!). Notamment le même fragment de code (création d'un couple ID_personne/ID_métier) apparaît à chaque test. Comment pourrais-je "optimiser" cette procédure? avec quelles outils? Peut-on intégrer des fonctions à l'intérieur des procédures?...
Merci.

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
 
ALTER PROCEDURE [dbo].[creer_personne_plusieurs_tables]
	-- paramètres procédure
	@Nom nchar(10), @Prenom nchar(10), @Tel varchar(50), @Metier nvarchar(50)
AS
 
--1er CAS : Metier connu et Nom connu : =ajouter un métier connu à 1 personne déjà connue.
	IF EXISTS (SELECT Nom FROM Liste WHERE Nom=@Nom) AND EXISTS (SELECT Metier FROM Metier WHERE Metier=@Metier)
 
	BEGIN
		--insertion d'un nouveau couple ID_personne/ID_métier à la table personne_metier
		INSERT INTO personne_metier (ID_personne, ID_metier)
 
				SELECT l.ID_personne, m.ID_metier FROM Liste l, Metier m
				WHERE 
					m.metier=@Metier and l.nom=@Nom
 
	END
 
 
--2ème CAS : Metier connu et Nom inconnu
	IF NOT EXISTS (SELECT Nom FROM Liste WHERE Nom=@Nom) AND EXISTS (SELECT Metier FROM Metier WHERE Metier=@Metier)
 
	BEGIN
		--insertion du nouveau nom dans la table Liste : création d'un ID correspondant.
		INSERT INTO [Annuaire].[dbo].[Liste]
           ([Nom]
           ,[Prénom]
           ,[Téléphone])
         SELECT
			@Nom,@Prenom,@Tel
 
		--insertion du nouveau couple d'ID (ID de la nouvelle personne et ID du métier déjà connu)
		INSERT INTO personne_metier (ID_personne, ID_metier)
 
				SELECT l.ID_personne, m.ID_metier FROM Liste l, Metier m
				WHERE 
					m.metier=@Metier and l.nom=@Nom
 
	END
 
 
--3ème CAS : Nom connu et Metier inconnu
	IF EXISTS (SELECT Nom FROM Liste WHERE Nom=@Nom) AND NOT EXISTS (SELECT Metier FROM Metier WHERE Metier=@Metier)
 
	BEGIN 
		 --insertion du nouveau métier dans la table métier
		 INSERT INTO [Annuaire].[dbo].[Metier]
			    ([Metier])
		        SELECT @Metier
 
		 --insertion du nouveau couple d'ID (métier/personne)
		 INSERT INTO personne_metier (ID_personne, ID_metier)
 
				SELECT l.ID_personne, m.ID_metier FROM Liste l, Metier m
				WHERE 
					m.metier=@Metier and l.nom=@Nom
	END
 
--4ème CAS : Nom inconnu et métier inconnu
	IF NOT EXISTS (SELECT Nom FROM Liste WHERE Nom=@Nom) AND NOT EXISTS (SELECT Metier FROM Metier WHERE Metier=@Metier)
 
	BEGIN
		--insertion du nouveau nom dans la table Liste : création d'un ID correspondant.
		INSERT INTO [Annuaire].[dbo].[Liste]
           ([Nom]
           ,[Prénom]
           ,[Téléphone])
         SELECT
			@Nom,@Prenom,@Tel
 
		--insertion du nouveau métier dans la table métier
		 INSERT INTO [Annuaire].[dbo].[Metier]
			    ([Metier])
		        SELECT @Metier
 
		--insertion du nouveau couple d'ID (métier/personne)
		 INSERT INTO personne_metier (ID_personne, ID_metier)
 
				SELECT l.ID_personne, m.ID_metier FROM Liste l, Metier m
				WHERE 
					m.metier=@Metier and l.nom=@Nom
	END

Invité · 14/06/2010, 15h43

Envoyé par ed222

Comme vous pourrez le constater, elle est très longue au regard de ce qu'elle est censée faire (les tables ont très peu de ligne heureusement!). Notamment le même fragment de code (création d'un couple ID_personne/ID_métier) apparait à chaque test. Comment pourrais-je "optimiser" cette procédure? avec quelles outils? Peut-on intégrer des fonctions à l'intérieur des procédures?...

Si vous trouvez votre procédure trop longue, retirez les commentaires ! J'blague, c'est que je n'ai pas l'habitude de voir autant de commentaires...

Pour faire court, on fait de l’optimisation quand on a des problèmes de lenteurs. Dans votre cas, on peut certes disserter un peu sur la façon dont vous avez traiter le problème mais si ça marche correctement suivant votre cahier des charges, passez du temps sur des problèmes plus important que de gagner 2 millièmes de secondes en passant 1 journée sur cette procédure.
Est-ce que ça provoque des lenteurs pénalisantes ? Si non, passer à autre chose.
Et je rajouterais que votre code est correct.
J’ai vu des codes incroyables pour ne rien faire au final…

**ed222** · 14/06/2010, 15h57

Bonjour!

Les commentaires c'est parce que je suis nouveau dans le monde des BD (et de l'informatique pour être tout à fait honnête ;+) ) et justement j'ai besoin de répères qui pourraont me servir lors de prochaines procédures.
Effectivement, ça ne pose pas de problème dans ce cadre. (je fais mon stage de fin d'études et mon tuteur me fait faire des petits exos pour le moment afin de prendre en main sql server). Il m'a donc fait la remarque que si je devais travailler avec des bases beaucoup plus lourdes (ce qui risque de ne pas tarder

, ce genre de code à rallonge pourrait poser problème...). D'où l'idée d'essayer de prendre les bonnes habitudes.

merci,

Invité · 14/06/2010, 17h12

Envoyé par ed222

Il m'a donc fait la remarque que si je devais travailler avec des bases beaucoup plus lourdes (ce qui risque de ne pas tarder

, ce genre de code à rallonge pourrait poser problème...). D'où l'idée d'essayer de prendre les bonnes habitudes.

C’est quoi le titre de ton tuteur?
Peut-être remet-il en cause ton modèle de données ?
Il faudrait qu’il nous explique où est le problème « avec des bases lourdes ». Si le but de ta procédure est d’insérer une nouvelle personne en prenant en compte les dépendances, je ne connais pas trop comment on peut faire mieux.
Certes, si le but est d’insérer massivement des personnes, on s’y prendrait probablement autrement suivant les sources de données.

**ed222** · 15/06/2010, 09h08

Bonjour,

mon modèle de données est le sien...

il m'a parlé de déclaration de variables et il a surtout insisté sur le fait que dans chaque cas, le même bout de code était répété. Mais il est vrai que ça fonctionne.

merci

**elsuket** · 15/06/2010, 11h04

Bonjour,

Et il a eu raison.

En effet dans tous les cas :

- si le nom de la personne ou le métier n'existe pas, vous allez l'ajouter;
- si la personne n'a pas de métier, vous allez ajouter cette occurrence de la relation.

Donc autant le tester une fois pour toutes

D'autre part, lorsque vous écrivez :

Code :

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

1
2
SELECT l.ID_personne, m.ID_metier FROM Liste l, Metier m
WHERE  m.metier=@Metier AND l.nom=@Nom

D'une part vous ne respectez pas la norme la plus récente qui veut que les jointures soient spécifiées à l'aide du mot-clé JOIN (dans votre cas CROSS JOIN), ce qui permet de séparer ces prédicats des filtres de la requête, qui doivent être dans le WHERE.
D'autre part, comme vous faites un produit cartésien, cela est coûteux.

Je parie que la colonnes ID_personne de la table Liste est de type entier auto-incrémenté, et qu'il en est de même pour la colonne ID_Metier de la table Metier.
Pour le savoir :

Code :

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

1
2
SELECT COLUMNPROPERTY(OBJECT_ID('Liste'), 'ID_personne', 'IsIdentity')
SELECT COLUMNPROPERTY(OBJECT_ID('Metier'), 'ID_Metier', 'IsIdentity')

Si tel est le cas, après insertion d'une ligne, vous pouvez récupérer la valeur du compteur à l'aide de la fonction SCOPE_IDENTITY().

Ensuite, vous n'avez pas qualifié les objets par le nom du schéma auquel ils appartiennent, ce qui, lorsque c'est fait, permet de gagner quelques pouillèmes puisqu'alors le moteur de base de données n'est pas au moins obligé d'interroger les tables de métadonnées pour connaître le schéma par défaut.
Donc quand vous manipulez des objets (tables, fonctions, procédures stockées, ...), précédez les par dbo, ce qui est votre cas, ou par le nom du schéma adéquat.
Sachez qu'il en existe au moins deux autres : sys et INFORMATION_SCHEMA.

Enfin, choisissez de mettre les crochets au nom des objets ou pas, mais conformez-vous y tout le temps

Finalement votre procédure stockée pourrait être :

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
ALTER PROCEDURE [dbo].[creer_personne_plusieurs_tables]
	@Nom nchar(10)
	, @Prenom nchar(10)
	, @Tel varchar(50)
	, @Metier nvarchar(50)
AS
BEGIN
	DECLARE @ID_personne int
		, @ID_metier int
 
	------------------------------------------
	-- Vérifie si le nom et le métier existent
	------------------------------------------
	SELECT	@ID_personne = ID_personne
	FROM	dbo.Liste
	WHERE	nom = @Nom
 
	SELECT	@ID_metier = ID_Metier
	FROM	dbo.Metier
	WHERE	Metier = @Metier
 
	-- Si la personne n'est pas encore enregistrée
	-- on l'ajoute
	IF @ID_personne IS NULL
	BEGIN
		INSERT INTO dbo.Liste
		(
			Nom
			, Prénom
			, Téléphone
		)
		SELECT @Nom
			, @Prenom
			, @Tel
 
		SELECT @ID_personne = SCOPE_IDENTITY()
	END
 
	-- Si le métier n'est pas encore enregistré
	-- on l'ajoute
	IF @ID_metier IS NULL
	BEGIN
		INSERT INTO dbo.Metier (Metier)
		SELECT @Metier
 
		SELECT @ID_metier = SCOPE_IDENTITY()
	END
 
	----------------------------------
	-- Ajoute un métier à une personne
	----------------------------------
	-- insertion du nouveau couple d'ID
	-- (ID de la nouvelle personne et ID du métier déjà connu)	
	INSERT INTO dbo.personne_metier
	(
		ID_personne
		, ID_metier
	)
	SELECT	@ID_personne
		, @ID_metier
END

Notez que pour le dernier INSERT, on ne vérifie pas si l'association existe déjà, ou qu'une personne ne peut avoir qu'un seul métier.
Mais là encore je parie que votre tuteur a mis une contrainte de clé primaire ou d'unicité, qui fait qu'il ne peut pas y avoir de doublons dans la table

@++

**ed222** · 15/06/2010, 15h25

Bonjour!

Je parie que la colonnes ID_personne de la table Liste est de type entier auto-incrémenté, et qu'il en est de même pour la colonne ID_Metier de la table Metier.

C'est en effet la cas

Pour les JOIN, c'est vrai qu'on m'a appris avec les WHERE façon old-school. Je vais devoir m' y mettre.

Un grand merci pour toutes ces précisions encore une fois! vraiment gentil.

Edouard.

**elsuket** · 15/06/2010, 16h39

Avec plaisir !

@++

**Sergejack** · 15/06/2010, 17h30

J'ai pas tout lu mais jette un oeil sur la clause OUTPUT de INSERT et DELETE.
Par ailleurs, plutôt que de faire des IF, tu peux gérer entièrement tes DML avec des WHERE.

optimiser une procédure stockée

Développement SQL Server

Discussions similaires

Partager

Partager