optimisation d'un processus d'insertion en base

**jbrasselet** · 07/12/2007, 09h51

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** · 07/12/2007, 09h58

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** · 07/12/2007, 10h05

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** · 07/12/2007, 10h12

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** · 07/12/2007, 10h19

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** · 07/12/2007, 10h22

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** · 07/12/2007, 10h28

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 ....)

**jbrasselet** · 07/12/2007, 12h59

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** · 07/12/2007, 13h18

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** · 07/12/2007, 15h31

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

**Ditch** · 07/12/2007, 15h41

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

**jbrasselet** · 07/12/2007, 15h47

arf vous me mettez tous le doute là

je ferais bien un bench si j'avais le temps

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?

)

**Keihilin** · 07/12/2007, 16h02

Envoyé par Ditch

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

Saint-SQL pardonne-lui...

**jbrasselet** · 07/12/2007, 16h03

Envoyé par Keihilin

Saint-SQL pardonne-lui...

mais mais mettez vous d'accord

**Ditch** · 07/12/2007, 16h05

Envoyé par Keihilin

Saint-SQL pardonne-lui...

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

**Keihilin** · 07/12/2007, 16h10

Envoyé par Ditch

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

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

optimisation d'un processus d'insertion en base

VB.NET

Vue hybride

Discussions similaires

Partager

Partager