Insertion multiple : quelle solution?

**Gold.strike** · 11/04/2012, 18h01

Bonjour,

Nous développons un module de synchronisation qui alimente notre base de données à partir des données fournies par un prestataire.

Dans un premier temps, nous créons une copie de la base de données du prestataire, via des appels à ses webservices.
Dans un deuxième temps, nous faisons un transformation ETL entre la base "prestataire" et notre base de données.

Actuellement, les 2 opérations sont très longues. Ces mauvaises performances s'expliquent en partie par le fait que les requêtes d'insertion/mises à jour sont exécutées individuellement...

Par exemple, pour remplir une des tables de la base "prestataire", nous utilisons la méthode 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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
public void updateGroupTeam(bool isGroup, int id, int club_id)
{
	using (NpgsqlConnection c = (NpgsqlConnection)conn.getConnexion())
	{
		using (NpgsqlCommand command = new NpgsqlCommand())
		{
			c.Open();
			command.Connection = c;
			command.Parameters.Clear();
			int n = -1;
			// update, sinon insertion
			command.CommandText = " UPDATE group_team  SET  id=id WHERE team_id=:team_id AND group_id = :group_id ";
			command.Parameters.Add(new NpgsqlParameter("group_id", DbType.Int64)).Value = (long)id;
			command.Parameters.Add(new NpgsqlParameter("team_id", DbType.Int64)).Value = (long)club_id;
			try
			{
				n = command.ExecuteNonQuery();
			}
			catch (Exception e)
			{
				log.Error("updateGroupTeam() UPDATE : " + e.Message);
			}
 
			if (n <= 0)
			{
				// insertion
				command.CommandText = " INSERT INTO group_team( team_id, group_id) VALUES ( :team_id, :group_id);";
				command.Parameters.Clear();
				command.Parameters.Add(new NpgsqlParameter("group_id", DbType.Int64)).Value = (long)id;
				command.Parameters.Add(new NpgsqlParameter("team_id", DbType.Int64)).Value = (long)club_id;
				try
				{
					n = command.ExecuteNonQuery();
				}
				catch (Exception e)
				{
					log.Error("updateGroupTeam() INSERT : " + e.Message);
				}
			}
		}
	}
}

Cette méthode est donc appelé pour chaque ligne de cette table "group_team". Je souhaiterais à la place utilisée de l'insertion multiple.

Pour cela, j'hésite entre :
- utiliser des transactions
- créer une requête qui contient toutes les lignes à insérer, afin d'utiliser une seule "Connection".

Je suis en train de faire des tests en utilisant la deuxième méthode : une requête contenant toutes les lignes à insérer.

Je me demande cependant comment gérer à la fois l'update et l'insert? En effet, avec la méthode actuelle, on sait que si une ligne ne peut pas être updatée, c'est qu'elle doit être insérée. Mais comemnt gérer ce cas dans le cas d'une insertion multiple?
- dois je d'abord faire un update sur l'ensemble des lignes, puis un insert de l'ensemble des lignes?
- peut on récupérer les lignes "posant problème" sans interrompre le traitement du reste des lignes?

Merci d'avance,

**Graffito** · 12/04/2012, 01h33

On peut faire de la modification multiple (Update, Insert,Delete) assez simplement via un DataAdapter (+ DataSet et DataTable).

**Er3van** · 12/04/2012, 09h25

Si c'est pour une question de performances, je dirais :

Utiliser des procédures stockées
Affiner le plan d’exécution (en créant des index?) de celles-ci via SSMS
Appel de ta procédure stockée N fois depuis la même connexion SQL

Sinon, tu peux voir du côté Bulk SQL

**Gold.strike** · 12/04/2012, 09h28

Envoyé par Graffito

On peut faire de la modification multiple (Update, Insert,Delete) assez simplement via un DataAdapter (+ DataSet et DataTable).

Garaffito, aurais tu un lien ou un exemple?

Dans mon cas, je n'ai pas toujours un DataSet ou un DataTable en source, mais parfois un HashSet. Il faudrait donc que je construise ce DataSet?

Voici la méthode actuellement utilisée pour l'insertion multiple :

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
 
public void insertGroupTeam(HashSet<int> HashSet_MembersId, Boolean isGroup, long idClub)
{
	bool bFirst = true;
	String stringInsert = String.Empty;
	// On construit un string avec les valeurs d'ajout pour chaque entrée
	foreach (int idMember in HashSet_MembersId)
	{
		if (!bFirst) 
			stringInsert += ",";    
		stringInsert += "('" + idClub + "','" + idMember + "','-1')";    
		bFirst = false;
	}
 
	if (String.IsNullOrEmpty(stringInsert))
		return;
 
	using (NpgsqlConnection c = (NpgsqlConnection)conn.getConnexion())
	{
		using (NpgsqlCommand command = new NpgsqlCommand())
		{
			int n = 0;
			c.Open();
			command.Connection = c;
			command.CommandText = " INSERT INTO group_team( team_id, group_id, round_id) VALUES " + stringInsert + ";";
			try
			{
				n = command.ExecuteNonQuery();
			}
			catch (Exception e)
			{
				log.Error("updateGroupTeam() INSERT : " + e.Message);
			}
		}
	}
}

Mais ce code ne me satisfait pas, car on y fait un INSERT, mais d'UPDATE. Et en cas d'erreur sur une des insertions (valeur déja présente par exemple), le reste des insertions n'est pas réalisée...

**Bluedeep** · 12/04/2012, 09h39

Bonjour

A vrai dire, on a un peu de mal à voir à quoi sert le code C# ici.

Visiblement, tu te contentes de passer des données de table à tables, donc je ne vois pas pourquoi tu ne te limites pas au SQL pour cela. (ce sera beaucoup plus rapide et plus logique : faire des allez-retour entre le DBMS et le serveur d'application semble de peu d'interêt ici).

**Gold.strike** · 12/04/2012, 09h48

Envoyé par Er3van

Si c'est pour une question de performances, je dirais :

Utiliser des procédures stockées
Affiner le plan d’exécution (en créant des index?) de celles-ci via SSMS
Appel de ta procédure stockée N fois depuis la même connexion SQL

Sinon, tu peux voir du côté Bulk SQL

Les procédures stockées me semblent effectivement pouvoir être une solution. Mais peut on définir directement dans la procédure que l'on souhaite faire un UPDATE, puis un INSERT si la ligne n'existe pas?

Envoyé par Bluedeep

Bonjour

A vrai dire, on a un peu de mal à voir à quoi sert le code C# ici.

Visiblement, tu te contentes de passer des données de table à tables, donc je ne vois pas pourquoi tu ne te limites pas au SQL pour cela. (ce sera beaucoup plus rapide et plus logique : faire des allez-retour entre le DBMS et le serveur d'application semble de peu d'interêt ici).

Je ne fais pas que passer des données de table à table. En fait, il s'agit d'un module de synchronisation qui va alimenter notre base de données, à partir d'infos récupérées chez un prestataire. Cela se fait en plusieurs étapes :

1 - Création d'une base de données "clonée" du prestataire à partir des données récupérées via les WebServices
2 - Alimentation de notre base de données, avec des transformations ETL à partir de la base "clonée"

Il y a donc pas mal de régles métiers à mettre en place, et de tests à réaliser afin d'éviter les doublons, ou d'enrichir certaines données du prestataire...

L'architecture a été définie par une société qui avait le développement à sa charge, mais qui ne l'a pas finalisé. Leurs choix n'ont sans doute pas été les meilleurs, mais je ne peux pas repartir de zéro.

Je cherche néanmoins à optimiser le traitement comme je peux.

**Er3van** · 12/04/2012, 10h02

Envoyé par Gold.strike

Les procédures stockées me semblent effectivement pouvoir être une solution. Mais peut on définir directement dans la procédure que l'on souhaite faire un UPDATE, puis un INSERT si la ligne n'existe pas?

Oui, T-SQL reste un language de programmation. Tu peux tout à fait avoir des conditions ou des appels à des méthodes dans ta procédure stockée.

**Bluedeep** · 12/04/2012, 10h09

Envoyé par Gold.strike

Les procédures stockées me semblent effectivement pouvoir être une solution. Mais peut on définir directement dans la procédure que l'on souhaite faire un UPDATE, puis un INSERT si la ligne n'existe pas?.

Comme le dit Er3van, T-SQL est un langage de programmation ("Turing complet"), donc tu codes à peu près tout ce que tu veux, tant que ce n'est pas de l'IHM (même si ce point n'est pas totalement impossible, il faudrait déjà être vicieux

)

**Bluedeep** · 12/04/2012, 10h12

Envoyé par Gold.strike

En fait, il s'agit d'un module de synchronisation qui va alimenter notre base de données, à partir d'infos récupérées chez un prestataire. Cela se fait en plusieurs étapes :

1 - Création d'une base de données "clonée" du prestataire à partir des données récupérées via les WebServices
2 - Alimentation de notre base de données, avec des transformations ETL à partir de la base "clonée"

Il y a donc pas mal de régles métiers à mettre en place, et de tests à réaliser afin d'éviter les doublons, ou d'enrichir certaines données du prestataire...

Je ne lit absolument rien ici qui contredise ma remarque : ce que tu décris correspond précisément à du développement à faire sur la base.

L'architecture a été définie par une société qui avait le développement à sa charge, mais qui ne l'a pas finalisé. Leurs choix n'ont sans doute pas été les meilleurs, mais je ne peux pas repartir de zéro.

Ca d'accord, c'est un argument recevable.

**Gold.strike** · 12/04/2012, 10h44

Envoyé par Bluedeep

Comme le dit Er3van, T-SQL est un langage de programmation ("Turing complet"), donc tu codes à peu près tout ce que tu veux, tant que ce n'est pas de l'IHM (même si ce point n'est pas totalement impossible, il faudrait déjà être vicieux

)

Envoyé par Er3van

Oui, T-SQL reste un language de programmation. Tu peux tout à fait avoir des conditions ou des appels à des méthodes dans ta procédure stockée.

Le T-SQL semble réservé aux SGBD de Microsoft et de Sybase, alors que nous utilisons Postgre SQL. Néanmoins il y a un équivalent coté Postgre : PL/pgSQL.
Maintenant les 2 offrent ils les mêmes posssibilités?

Envoyé par Bluedeep

Je ne lit absolument rien ici qui contredise ma remarque : ce que tu décris correspond précisément à du développement à faire sur la base.

J'aurais encore quelques questions à ce sujet :
- peut on déclarer et utiliser des webservices?
- peut on gérer des logs d'erreurs/de suivi?
- peut on "batcher" une procédure stockée?

Si ces différents points sont réalisables depuis un tel langage, il aurait effectivement présenté pas mal d'avantages...

**calagan99** · 12/04/2012, 11h02

Bonjour,

Pour la gestion "unique" des INSERT ou UPDATE, regarde du côté de la clause MERGE.

Sinon, l'utilisation de requêtes paramétrées est plus souhaitable que les concaténations de chaînes. Ca ne va pas révolutionner les perfs, mais ce sera déjà ça.

**Er3van** · 12/04/2012, 11h03

Envoyé par Gold.strike

Le T-SQL semble réservé aux SGBD de Microsoft et de Sybase, alors que nous utilisons Postgre SQL. Néanmoins il y a un équivalent coté Postgre : PL/pgSQL.
Maintenant les 2 offrent ils les mêmes posssibilités?

J'avais fait le postulat que la base était sur SQL Server... mais PL/pgSQL est aussi développé et te permettra a priori de faire la même chose.

Envoyé par Gold.strike

J'aurais encore quelques questions à ce sujet :
- peut on déclarer et utiliser des webservices?
- peut on gérer des logs d'erreurs/de suivi?
- peut on "batcher" une procédure stockée?

Si ces différents points sont réalisables depuis un tel langage, il aurait effectivement présenté pas mal d'avantages...

Des WebServices via une procédure stockée ? Si c'est possible, je pense que ça ne peut pas l'être exclusivement via une procédure stockée... tu dois être obligé de passer par des programmes tierces. Bluedeep in/confirmera !

Pour les logs, oui c'est tout à fait possible, ainsi que l'ordonnancement de ta procédure (si c'est ce que tu appelles "batchage").

**Bluedeep** · 12/04/2012, 12h03

Envoyé par Gold.strike

Le T-SQL semble réservé aux SGBD de Microsoft et de Sybase, alors que nous utilisons Postgre SQL. Néanmoins il y a un équivalent coté Postgre : PL/pgSQL.

Désolé, j'ai rebondi bêtement sur le post de Er3van, alors qu'une lecture même superficielle du code source montre en effet que tu utilise POstGreSql......

Maintenant les 2 offrent ils les mêmes posssibilités?

N'ayant pas pratiqué PostGre depuis ... 7 ans, je ne peux pas répondre précisément.
Mais on peut le supposer (je ne connais que T-SQL et PL/SQL, le langage Oracle).

J'aurais encore quelques questions à ce sujet :
- peut on déclarer et utiliser des webservices?

Là c'est pour moi difficile de répondre.

Sous Oracle, En PL/SQL c'est possible (via le package UTL_HTTP).
Sous Sql Server,c'est possible également mais avec SQLCLR, pas en T-SQL (Microsoft considère ce type de fonctionnalité comme un gros trou de sécurité) ou en passant par le Service Broker.

Dans le cas de PostGre je suis incapable de répondre.

- peut on gérer des logs d'erreurs/de suivi?

Bien sur !

- peut on "batcher" une procédure stockée?

Cette fonctionnalité existe de base sur Sql Server et Oracle (les "jobs" et le "scheduler"). Je pense que cela doit exister aussi sur PostGre (le contraire serait étonnant).

Si ces différents points sont réalisables depuis un tel langage, il aurait effectivement présenté pas mal d'avantages...

Je te propose de nous exposer ton archi plus en détails, en différenciant ce qui est prévu de ce qui est fait. Ici on a clairement pluis un problème d'archi que de programmation.

**Gold.strike** · 12/04/2012, 15h06

Envoyé par Bluedeep

Je te propose de nous exposer ton archi plus en détails, en différenciant ce qui est prévu de ce qui est fait. Ici on a clairement pluis un problème d'archi que de programmation.

L'interface de synchronisation que nous développons est destiné à alimenter les données d'un progiciel que nous développons : un outil de gestion de club de football. Ce progiciel est lui même développé en .Net : C#, Winform et WPF.
Le fait de disposer d'une base de données élargie est l'un des intérèts de la solution. Une interface de synchronisation a donc été développée afin de récupérer les données au près d'un prestataire spécialisé. Ce développement avait été confié à une société qui nous a livré un produit pas totalement fini, et loin d'être optimisé.
Cette interface se présente donc actuellement comme un projet indépendant de la solution globale.

Pour exposer succintement l'architecture, nous avons donc 2 bases de données, toutes 2 hébergées sur le même serveur sous Postgre SQL 9 :
- 1 base contenant les données fournies par un prestataire, alimentée via des webservices : BASE_PRESTATAIRE
- 1 base contenant nos données ET celles du prestataire, qu'on mettra à disposition de nos clients : BASE_SOURCE

La BASE_PRESTATAIRE contient une quinzaine de tables, dont une table "synchronisation", qui stocke les correspondances entre un enregistrement de BASE_PRESTATAIRE et un enregistrement de BASE_SOURCE.
Les données sont récupérées via des WebServices, qui nous renvoient un XML. Le XML est ensuite deserialisé et inséré ligne par ligne dans cette base.
La plupart de ses tables reprennent même les identifiants du prestataires, sauf pour 3 tables de "relation" (dont les données sont identifiés uniquement par les clés étrangères).
Il y a une fonction trigger, qui mets à jour la table "synchronsiation", après chaque opération sur les autres tables.

=> il y a très peu de transformation entre les données du prestataire et cette table, les seules transformations réalisées sont présentes pour faciliter la deuxième étape.

La BASE_SOURCE contient elle près de 150 tables. Le module de synchronisation que nous développons mets à jour une vigntaine de ces tables. Ces tables peuvent donc être alimentées par nous (si on effectue de la saisie) ou par le prestataire (par le biais de la synchronisation).
Les données sont récupéres depuis la BASE_PRESTATAIRE, testées pour éviter des doublons, traitées pour s'adapter à notre format de base de données, et insérées ligne par ligne.

=> cette étape est beaucoup plus longue que la précédente, car il y a beaucoup plus de travail pour chaque ligne traitée.

Nous nous sommes rendus compte que la société ayant développé cette interface pour nous réalise des insertions ligne par ligne, ce qui un impact conséquent sur le temps de traitement.

Nous avons ainsi réalisé une mesure toute simple sur l'insertion d'une vingtaine de lignes dans la BDD :
- insertion ligne par ligne : 4.20 secondes
- insertion multiple : 0.45 secondes

Nous souhaitons donc généraliser l'insertion multiple, mais ne savons pas comment gérer le fait de d'aborder exécuter un UPDATE, puis un INSERT pour l'ensemble des lignes traitées.

**Gold.strike** · 13/04/2012, 10h48

Je viens de créer une procédure stockée gérant àl a fois l'update, et l'insert :

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
 
CREATE FUNCTION merge_group_team(teamid BIGINT, groupid BIGINT, roundid BIGINT) RETURNS VOID AS 
$$ 
BEGIN 
    LOOP 
        -- first try to update the row 
        UPDATE group_team SET id = id 
        WHERE team_id = teamid AND group_id = groupid AND round_id = roundid; 
        IF found THEN 
            RETURN; 
        END IF; 
        -- not there, so try to insert the key 
        -- if someone else inserts the same key concurrently, 
        -- we could get a unique-key failure 
        BEGIN 
            INSERT INTO group_team(team_id,group_id,round_id) VALUES (teamid, groupid, roundid); 
            RETURN; 
        EXCEPTION WHEN unique_violation THEN 
            -- do nothing, and loop to try the UPDATE again 
        END; 
    END LOOP; 
END; 
$$ 
LANGUAGE plpgsql;

J'arrive donc à utiliser cette fonciton pour une insertion simple, mais toujours pas pour des insertions mutliples...

Que faut il faire pour pouvoir l'utiliser avec des insertions multiples?

Merci,

**youtpout978** · 13/04/2012, 11h02

je ne sais pas ce qui est possible en postgre mais peut être peux tu créer des variables de type table et donc tu construis ta table du côté c# et tu la passe en paramètre à ta proc stock (ta procédure stockée ne prendra plus en paramètre des bigint mais une table ou 3 tables qui correspondent à tes list d'id)

**Er3van** · 13/04/2012, 11h44

Si pour utiliser l'insertion multiple tu dois remplir une table temporaire avant... je ne vois pas bien le gain !

A priori, je ne vois pas d'autre option que d'appeler N fois cette méthode.
Mais comme je le disais plus haut, si tu appelles 100 fois la même procédure stockées dans la même connexion, tu gagneras quand même beaucoup de temps par rapport à ta solution initiale.
Par contre, si tu pioches des infos dans différentes tables pour faire ta création (donc depuis ta procédure stockée) penses à créer les index qui vont bien.

**youtpout978** · 13/04/2012, 14h13

je ne parlais pas de table temporaire du côté bdd mais de la construire en c# et de la passer en paramètre à la proc stock

**Gold.strike** · 13/04/2012, 15h15

C'est ce que je viens de tester, après avoir cherché plusieurs solutions sur internet.

Je passe donc bien en paramètre un DataTable, ayant cette structure :

Code :

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

1
2
3
4
5
 
DataTable dtGroupTeam = new DataTable();
dtGroupTeam.Columns.Add("teamid", typeof(long));
dtGroupTeam.Columns.Add("groupid", typeof(long));
dtGroupTeam.Columns.Add("roundid", typeof(long));

J'ai ensuite une méthode qui appelle ma procédure stockée, en traitant chaque ligne de ma table à travers un DataAdapter :

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
 
public void insertGroupTeamProcedure(DataTable dtGroupTeam)
{
	using (NpgsqlConnection c = (NpgsqlConnection)conn.getConnexion())
	{
		using (NpgsqlCommand command = new NpgsqlCommand("merge_group_team", c))
		{
			command.CommandType = CommandType.StoredProcedure;
			command.UpdatedRowSource = UpdateRowSource.None;
 
			command.Parameters.Clear();
			command.Parameters.Add("@teamid", NpgsqlTypes.NpgsqlDbType.Bigint, 4, dtGroupTeam.Columns[0].ColumnName);
			command.Parameters.Add("@groupid", NpgsqlTypes.NpgsqlDbType.Bigint, 4, dtGroupTeam.Columns[1].ColumnName);
			command.Parameters.Add("@roundid", NpgsqlTypes.NpgsqlDbType.Bigint, 4, dtGroupTeam.Columns[2].ColumnName);
 
			NpgsqlDataAdapter adpt = new NpgsqlDataAdapter();
			adpt.InsertCommand = command;
			c.Open();
			try
			{
				int recordsInserted = adpt.Update(dtGroupTeam); 
			}
			catch (Exception e)
			{
				log.Error("Erreur : " + e.Message);
			}
		}
	}
}

J'ai essayé de pramaétré le InsertCommand de mon DataAdapter, mais cela me retourne une exception "NotSupportedException", car "la méthode n'est pas prise en charge" :

Code :

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

1
2
3
 
// Specify the number of records to be Inserted/Updated in one go. Default is 1.
adpt.UpdateBatchSize = 50;

De plus, je ne comprends pas non plus comment valoriser la propriété "size" de la commande Paramaters.Add. J'ai pour l'instant mis 4 par défaut et cela semble marcher :

Code :

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

1
2
 
command.Parameters.Add("@roundid", NpgsqlTypes.NpgsqlDbType.Bigint, 4, dtGroupTeam.Columns[2].ColumnName);

Enfin, les performances pour le traitement de 20 lignes via le DataAdapter restent supérieures à celles de la méthode d'origine (traitement ligne à ligne), mais sont inférieures à celle d'une insertion multiple via une chaine de caractère :
- ancienne méthode (insertion simple) : 4.1 secondes
- insertions multiples (procédure stockée) : 0.9 seconde
- insertions multiples (chaine) : 0.5 seconde

Est il possible d'optimiser les résultats obtenus avec la procédure?

Merci,

**calagan99** · 13/04/2012, 16h31

Le gain est déjà significatif.
Maintenant, 1 seconde par enregistrement c'est long, en effet.

Avez-vous jeté un coup d'oeil aux optimisations possibles côté base de données ?

D'autre part, je relance l'idée déjà émise : plutôt que de tenter un UPDATE puis un INSERT et de (ne pas) gérer l'éventuelle exception levée, pourquoi ne pas passer par une syntaxe MERGE qui :
- fait le même boulot
- sans générer d'erreur
- et de façon optimisée.

Insertion multiple : quelle solution?

Accès aux données

Discussions similaires

Partager

Partager