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

**WOLO Laurent** · 07/05/2012, 16h23

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

Je crois qu'il s'agit de PostGreSQL ici, n'est ce pas ?

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

Insertion multiple : quelle solution?

Accès aux données

Vue hybride

Discussions similaires

Partager

Partager