Discussion :

[jbrasselet]

Bonjour,

j'ai une application de gestion de planning. En gros ma page affiche un tableau représentant mon planning.

J'ai en abscisse mes 5 jours ouvrés, chaque jour étant décomposé en 4 périodes (matin, 12H,13H et après-midi)
J'ai en ordonnée une liste d'agents allant de 5 à 20 (environ)

L'utilisateur va remplir chaque case pour affecter des activites/absences à chaque employé pour chaque période. Il y a donc de 100 à 400 données à enregistrer

Actuellement, lorsqu'il appuie sur enregistrer, je me retrouve avec un flux XML contenant toutes les données.
Pour chaque noeud (chaque case), je fais une insertion en base avec ouverture de la base, appel d'une procédure stockée et fermeture de la base.

Seulement ça met un temps assez long pour 400 données (environ 20 secondes)
Et bien évidemment c'est trop pour mon chef

Je cherches donc un moyen d'optimiser mon processus.
Je pense qu'ouvrir et fermer à chaque fois la base doit être pénalisant non?

Avez-vous déjà été eu ce genre de problème?

Merci d'avance

[Keihilin]

Alors effectivement, éviter de ouvrir/fermer 400 fois la connexion devrait déjà améliorer sensiblement les performances.

Cela dit, je pense qu'il y a un moyen d'accélerer drastiquement le processus en regroupant les requêtes au maximum.

Pour plus de détails, j'aurais besoin du shéma de la table dans laquelle tu fais les insert (des insert ou des updates ?).

[jbrasselet]

J'ai testé en ouvrant et fermant la connexion qu'une fois et cela ne change pas grand chose au final (1 à 2 secondes max)

Pour ma table la voici :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
create table PLNG_ANTENNE (
   IDPLNGANTENNE	int	identity(1,1)	not null,
   ID_AGENT		int		not null,
   ID_ACTIVITE		int		null,
   ID_MA			int		null,
   ID_ANTENNE		int		not null,
   TableTemps_ID		int		not null,
   constraint	PK_PLNG_ANTENNE		primary key	(IDPLNGANTENNE)
)

Les ID_AGENT, ID_ACTIVITE, ID_MA,ID_ANTENNE et TAbleTemps_ID sont des clés étrangères.

Voici le code de ma procédure stockée également :

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
 
-- =============================================
-- Author:		Julien Brasselet
-- Update date: 05/12/2007
-- Description:	Insertion d'une absence dans le planning d'antenne
-- =============================================
ALTER PROCEDURE [dbo].[AN_InsererAbsence]
	@idAntenne		int,
	@idAgent		int,
	@codeAbsence	char(10),
	@dateEntiere	char(25)
AS
	declare @idMa	int,
			@idJour int,
			@idPlng int,
			@heure	int,
			@indice int
BEGIN
	SET NOCOUNT ON;
 
	set @idPlng = 0
	set @indice = 1
	set @heure	= SUBSTRING(@dateEntiere,12,2)
 
	SELECT	@idMa = ID_MA
    FROM	MOTIF_ABSENCE
    WHERE	CODE_MA= @codeAbsence
 
	SELECT	@idJour = TableTemps_ID
    FROM	TableTemps
    WHERE	TableTemps_DateEntiere= @dateEntiere
 
	SELECT	@idPlng = IDPLNGANTENNE
    FROM	PLNG_ANTENNE
    WHERE	TableTemps_ID = @idJour
			AND id_agent = @idAgent
 
	IF @idPlng > 0
		BEGIN
			--UPDATE
			UPDATE	plng_antenne 
			SET		id_ma = @idMa,
					id_activite = NULL
			WHERE	id_agent = @idAgent
					AND TableTemps_ID = @idJour
					AND id_antenne = @idAntenne
 
			IF @heure = 8 OR @heure = 14
				BEGIN
					WHILE @indice < 4
						BEGIN
							SET @idJour = @idJour + 1
							SET @indice = @indice + 1
 
							UPDATE	plng_antenne 
							SET		id_ma = @idMa,
									id_activite = NULL
							WHERE	id_agent = @idAgent
									AND TableTemps_ID = @idJour
									AND id_antenne = @idAntenne
						END
				END
		END
	ELSE
		BEGIN
			--INSERT
			INSERT INTO plng_antenne (id_agent,id_ma,TableTemps_ID,id_antenne) 
			VALUES (@idAgent,@idMa,@idJour,@idAntenne)
 
			IF @heure = 8 OR @heure = 14
				BEGIN
					WHILE @indice < 4
						BEGIN
							SET @idJour = @idJour + 1
							SET @indice = @indice + 1
 
							INSERT INTO plng_antenne (id_agent,id_ma,TableTemps_ID,id_antenne) 
							VALUES (@idAgent,@idMa,@idJour,@idAntenne)
						END
				END
		END
END

Comment peut-on regrouper des requêtes?

[Bluedeep]

Déja, si tu as n enregistrement à effectuer "en vrac" cela n'a aucun sens d'ouvrir et de fermer la connexion.
De plus, tu utilises une transaction (celle implicite de la procécure stockée) pour chaque écriture, ce qui n'arrange pas les choses.

Donc :

- Ouvrir la connextion
- BeginTransaction
- Boucler sur les insertions
- Commit
- Fermer connexion

Gain estimé : 1000% à la louche

A savoir que c'est plus l'absence de transaction que l'ouverture de base ici qui pénalise; bien entendu cette ouverture de connexion ne te permet pas d'utiliser une transaction, mais, même si tu ne faisais qu'une seule ouverture en continuant à appeler les insertions hors transaction (ou, plus précisément, sur la transaction implicite de la procécure stockée), ton gain de performance sera relativement minime.

[Keihilin]

Arf J'avais pas vu que tu passes par une proc...

L'idée de regroupement était de construire une et une seule requête au lieu d'en faire 400. Le but étant d'arriver à quelque chose comme

Code :

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

1
2
3
4
5
6
7
8
 
INSERT INTO T_TABLE(C_1, C_2, C_3)
SELECT 1, 2, 3, 4
UNION 
SELECT 5, 6, 7 ,8
UNION
SELECT 9, 10, 11, 12
...

gain de perfs assuré...

Par contre là, avec ta procédure c'est plus compliqué

Tu es sous quelle version de SQL Server ?

[jbrasselet]

Keihilin > Je suis sous SQL Server 2005.

Bluedeep > en gros tu me conseilles de ne plus passer par la proc stock et de faire mes requêtes de sélection et ma requête d'insertion à la volée?

[Bluedeep]

Bluedeep > en gros tu me conseilles de ne plus passer par la proc stock et de faire mes requêtes de sélection et ma requête d'insertion à la volée?

Oui. A vrai dire, j'aime bien les proc. stoc., mais je limite en général leur utilisation à des cas "bénéfiques" en terme de perf (grosse mise à jour complexe, faites à partir de données intégralement en base, qui exploite dans ce cas le temps CPU du serveur de BDD plutôt que celui du poste client; exemple récent "vécu" : renormalisation & interpolation de quelques dizaines milliers de vecteurs).

Ici, oui, a priori, je n'utiliserais pas la proc.stoc., ou éventuellement, je la générerais "à la volée" pour en faire une "grosse" faisant l'ensemble des MAJ.

Autre possibilités redéfinir ta proc. stoc. pour qu'elle puisse faire des insert multiple à partir des infos que tu lui fournis en paramètres.



Mais, dans tous les cas, 400 insert sans intervention manuelle doivent être fait dans une transaction unique, tout autre choix est une faute de design.

Aprés, le choix d'une solution ou de l'autre dépend aussi de considérations architecturales (ta DAL est elle instanciée sur le poste client, ou sur un serveur d'application ? ce serveur est il distinct du serveur de SGBD ? etc, etc ....)

[Keihilin]

Autre possibilités redéfinir ta proc. stoc. pour qu'elle puisse faire des insert multiple à partir des infos que tu lui fournis en paramètres.

C'est bien là que l'on touche à la faiblesse des procs dans les versions de SQL Server antérieures à 2008: l'impossibilité de traiter simplement un tableau de données.

Si vraiment, pour des tas de raisons qui seraient parfaitement compréhensibles, tu dois absolument continuer à utiliser ta procédure stockées, je refléchirais à une stratégie en 2 étapes :

1. Insertion en bloc dans une table temporaire (selon l'exemple que je t'ai donné)
2. Utilisation de la table temporaire par ta proc. Laquelle ne serait lancée qu'une fois, pas 400.

[Bluedeep]

C'est bien là que l'on touche à la faiblesse des procs dans les versions de SQL Server antérieures à 2008: l'impossibilité de traiter simplement un tableau de données.

C'est pour cela que je lui suggérais aussi comme possibilité de générer sa proc stoc à la volée, avec les données "en dur" dedans, l'exécuter puis la dropper après exécution.

Mais il est clair que ca va être sensiblement plus lourd à coder qu'une insertion dans une transaction se dispensant de la proc stoc.

[jbrasselet]

Non je peux ne pas utiliser de proc stock dans ce cas.
Donc je vais tenter la transaction et l'insertion via des requêtes "simples".

Je vous tiens au courant.

NB : Il faudrait que je remettes le nez dans un article comparant les perfs entre proc stock et requêtes simples. Je m'étais basé sur l'avis d'un collègue qui m'avait conseillé de tout mettre en proc stock.

[Keihilin]

NB : Il faudrait que je remettes le nez dans un article comparant les perfs entre proc stock et requêtes simples. Je m'étais basé sur l'avis d'un collègue qui m'avait conseillé de tout mettre en proc stock.

Pas la peine de trop te torturer, les insert multiples sont un cas typique de "faiblesse" des procs à cause de leur incapacité à recevoir des lots de données en paramètre.
Cela dit, à mon sens le critère de performances est rarement déterminant dans le choix de passer par des procs ou pas.

[jbrasselet]

sur quels critères te bases-tu à ce moment là?

[Keihilin]

Parfois sur les performances, mais comme déjà dit, les procs ne sont pas systématiquement plus rapides que du SQL généré à la volée, et dans la balance, le gain potentiel ne vaut pas toujours le manque de souplesse que cela implique.

En bref, pour moi le critère déterminant c'est l'intégrité des données et la sécurité.

[Bluedeep]

Parfois sur les performances, mais comme déjà dit, les procs ne sont pas systématiquement plus rapides que du SQL généré à la volée, et dans la balance, le gain potentiel ne vaut pas toujours le manque de souplesse que cela implique.

En bref, pour moi le critère déterminant c'est l'intégrité des données et la sécurité.

De plus, deux autres considérations sont à prendre en compte :

- l'architecture applicative.
- l'architecture système


Reprenons le cas générique cité ici (un "bulk update") et considérons d'abord une archi classique client-serveur.

- Cas 1 : client poussif / serveur faiblement chargé / réseau pas très rapide, voire de fiabilité moyenne.

Là, une évidence s'impose : je dois faire bosser le serveur. Donc proc stoc. Maintenant, il est possible qu'il soit difficile voire impossible de désigner la proc stoc pour qu'elle recoive toutes les infos pour la tâche. Donc je peux générer une proc stoc à la volée avec les infos dedans et l'envoyer au serveur, demander son exécution et la dropper après. ca peut être une solution très efficace. Si en revanche il est facile de designer la proc stoc pour qu'elle bosse à partir de quelques paramètres, je la définie de manière perenne dans la base et l'utilise classiquement; mais dans tous les cas, ici, je veux utiliser une proc stoc.

Cas 2 : client péchu / serveur plus chargé / réseau local rapide et fiable

Je peux faire le choix exactement inverse : je vais faire gratter le client, donc transaction et sql exécuté "classiquement".


Bien entendu, je peux avoir toutes les nuances intermédiaires entre les deux cas "caricaturaux" cités

On peut aussi considérer la cas d'une archi avec la couche d'accés aux données s'exécutant sur une machine séparée (exemple client distant, serveur appli sur un lan commun avec le serveur SGBD); là encore je peux faire un choix différent.

Il n'y a pas de réponses toutes faites.

Néanmoins, je ne suis pas entiérement d'accord avec Keihilin quand il invoque l'argument de manque de souplesse : la génération "à la volée" de proc stoc offre autant de souplesse que l'envoie de requêtes sql et à peine plus de complexité.
J'ai remarqué que c'est une solution que peu de développeurs utilisaient; c'est dommage car elle offre un peu le meilleur des deux mondes.
Cependant, il ne faut pas perdre de vue que les ordres de création/drop de la proc stoc dans la base ne sont pas entiérement neutres en terme de CPU, donc à ne pas non plus utiliser à toutes les sauces. (de plus, le problème des autorisations peut venir compliquer les choses).

Gardons à l'esprit que, si il existait une solution universelle pour tous les cas, les autres seraient oubliées.

Un autre point sur l'argument "manque de souplesse" : les proc stoc (non générées à la volée, bien sur) peuvent avoir l'effet inverse; en effet, elles peuvent permettre à l'appli de supporter des modif du modèle de données par réécriture des proc stoc sans toucher à l'appli; bien entendu cet argument devient caduque avec les proc stoc générées à la volée.

[Keihilin]

Néanmoins, je ne suis pas entiérement d'accord avec Keihilin quand il invoque l'argument de manque de souplesse : la génération "à la volée" de proc stoc offre autant de souplesse que l'envoie de requêtes sql et à peine plus de complexité.

A ce stade de la lecture, j'avais déjà ma réponse en tête

...mais tu me l'enlève de la bouche :

(de plus, le problème des autorisations peut venir compliquer les choses).

C'est vrai qu'il s'agit là d'une méthode apportant une certaine souplesse au procs, le gros avantage étant de pouvoir bénéficier d'une transaction 100% sûre puisque exécutée sur le serveur, mais j'y vois quand même quelques défauts...

• Autorisations (comme déjà dit)
• Je m'interroge sur le comportement du serveur au niveau de la gestion de ses plans d'exécution dans ce cas de figure.
• Quid de l'impact sur les statistiques et les dépendances si on utilise cette technique de manière soutenue ?

[jbrasselet]

Bluedeep, tu aurais un exemple de génération de proc stock à la volée ou un lien expliquant ce procédé?

[Ditch]

Je n'ai pas tout lu mais personnellement je trouve la procédure stockée très ... couteuse en temps.

3 select pour récupérer des id c'est trop. Utilise les jointures et tu gagneras bien du temps!

Et pour mon avis: vive les procédures stockées dont le processus est stocké et mis en cache dans Sql Server

[jbrasselet]

Tu veux dire faire une requête plutôt du type :

Code :

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

1
2
3
4
5
6
7
 
UPDATE	plng_antenne 
SET id_ma = (SELECT ID_MA FROM MOTIF_ABSENCE WHERE CODE_MA= @codeAbsence),
    id_activite = NULL
WHERE id_agent = @idAgent
      AND TableTemps_ID = (SELECT TableTemps_ID FROM TableTemps WHERE TableTemps_DateEntiere= @dateEntiere)
      AND id_antenne = @idAntenne

avec les select imbriqués?

[Ditch]

Non plutôt un

Code :

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

1
2
3
4
UPDATE table
SET colonne = valeur
FROM table
INNER JOIN table2 ON table.champ = table2.champ

[jbrasselet]

Je pense que je vais me tourner evrs une solution sans proc stock de toute façon.
je vais écrire mon algo parce que ça va pas être simple mais surtout faut que je retravaille ma classe d'exécution de requêtes pour y incorporer la notion de transaction T_T

merci