optimisation d'un processus d'insertion en base

Version imprimable

07/12/2007, 09h51
jbrasselet

optimisation d'un processus d'insertion en base

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 :mouarf:

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
07/12/2007, 09h58
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 ?).

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:

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:

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?

07/12/2007, 10h12
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.
07/12/2007, 10h19
Keihilin
Arf 8O 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:

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 ?
07/12/2007, 10h22
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?
07/12/2007, 10h28
Bluedeep

Citation:

Envoyé par jbrasselet

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 ....)
07/12/2007, 10h38
Keihilin
Citation:

Envoyé par Bluedeep

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.
07/12/2007, 10h42
Bluedeep

Citation:

Envoyé par Keihilin

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.
07/12/2007, 10h50
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. :roll:
07/12/2007, 11h08
Keihilin

Citation:

Envoyé par jbrasselet

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. :roll:

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.
07/12/2007, 11h16
jbrasselet

sur quels critères te bases-tu à ce moment là?
07/12/2007, 11h20
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é.
07/12/2007, 11h48
Bluedeep

Citation:

Envoyé par 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é.

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.
07/12/2007, 11h57
Keihilin
Citation:

Envoyé par Bluedeep

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 :mrgreen:

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

Citation:

Envoyé par Bluedeep

(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 ?
07/12/2007, 12h14
jbrasselet

Bluedeep, tu aurais un exemple de génération de proc stock à la volée ou un lien expliquant ce procédé?
07/12/2007, 12h49
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 :)

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

Code:

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?

Non plutôt un

Code:

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

07/12/2007, 15h31
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
07/12/2007, 15h41
Ditch

Citation:

Envoyé par 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

A toi de voir mais je ne suis pas certain que tu sois gagnant avec cette solution ;)
07/12/2007, 15h47
jbrasselet

arf vous me mettez tous le doute là :aie:

je ferais bien un bench si j'avais le temps :roll:

Le problème Ditch si je gardes ma proc stock est comment la modifier. Certes tu donnes un exemple pour l'update mais il me faut tout de même tester si je dois faire un update ou un insert.
Dans le cas d'un insert il faut tout de même que j'aille chercher mes valeurs avec des select non?

(ou alors t'as une astuce de la mort? :D)
07/12/2007, 15h49
Ditch

Citation:

Envoyé par jbrasselet

arf vous me mettez tous le doute là :aie:

je ferais bien un bench si j'avais le temps :roll:

Prends le temps :p Mais faut faire la requête d'update correctement dans la procédure stockée afin de ne pas fausser le résultat :D
07/12/2007, 15h55
jbrasselet

Durant mon temps de boulot ça va être chaud :(
je verrais si j'ai le temps ce WE...

NB : j'ai édité mon post au dessus ditch ;)
07/12/2007, 16h01
jbrasselet

Bon j'ai tenté le coup de la transaction et des requêtes sql à la volée mais mes temps de réponse ne sont pas meilleurs.

Je vais tenter la modification de la proc stock avec une seule ouverture de connexion pour voir

Citation:
Envoyé par Ditch
Code:

1 2 3 4 UPDATE table SET colonne = valeur FROM table INNER JOIN table2 ON table.champ = table2.champ
Saint-SQL pardonne-lui...

07/12/2007, 16h03
jbrasselet

Citation:

Envoyé par Keihilin

Saint-SQL pardonne-lui...

mais mais mettez vous d'accord :aie:
07/12/2007, 16h05
Ditch

Citation:

Envoyé par Keihilin

Saint-SQL pardonne-lui...

Qu'est ce qui te gêne là dedans? Que ca fonctionne de manière rapide? :mouarf:
07/12/2007, 16h10
Keihilin

Citation:

Envoyé par Ditch

Qu'est ce qui te gêne là dedans? Que ca fonctionne de manière rapide? :mouarf:

C'est plus fort que moi, cette syntaxe me pique les yeux :mrgreen:
07/12/2007, 16h15
jbrasselet

Je pensais à un truc.
Sachant qu'à la base mes données sont dans un flux XML, je pourrais peut être passer par un dataset non?
07/12/2007, 16h16
Ditch

Citation:

Envoyé par Keihilin

C'est plus fort que moi, cette syntaxe me pique les yeux :mrgreen:

Et pourtant on la retrouve fréquemment dans différents livres sur l'optimisation. Personnellement, je ne vois pas en quoi cette syntaxe gène. C'est comme un select sauf qu'on dit quels champs on doit mettre à jour :D

Mais soit, personne n'est obligé de suivre le conseil :)