Discussion :

[zegreatclan]

Bonjour,

Je chercher à optimiser une application .NET qui insère de très nombreuses lignes dans une base postgresql. Il s'agit d'une boucle sur les enregistrements d'un très gros fichier xml et que je dois inserer dans ma base postgre.

Existe t'il un moyen d'optimiser l'insertion en base ? (il y a plus de 15millions d'enregistrements à ajouter ...) à cette échelle la moindre optimisation compte ...

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
 
XmlTextReader xmtr = new XmlTextReader(FileName);
 
        while (xmtr.Read())
        {
            if (xmtr.NodeType == XmlNodeType.Element)
            {
                if (xmtr.Name == "node")
                {
 
                    string nodeid = getAttribute(xmtr, "ref");
 
                    NpgsqlCommand NodeInsert = new NpgsqlCommand(
                        @"Insert into 
                            ""ImportNode"" (""Node_OSM_Id"",""Node_OSM_Lat"",""Node_OSM_Lon"") 
                            Values(@Node_OSM_Id,@Node_OSM_Lat,@Node_OSM_Lon)", sqlConnex);
                    NodeInsert.Parameters.Add(new NpgsqlParameter("@Node_OSM_Id", int.Parse(getAttribute(xmtr, "id"))));
                    NodeInsert.Parameters.Add(new NpgsqlParameter("@Node_OSM_Lat", decimal.Parse(getAttribute(xmtr, "lat").Replace(".", ","))));
                    NodeInsert.Parameters.Add(new NpgsqlParameter("@Node_OSM_Lon", decimal.Parse(getAttribute(xmtr, "lon").Replace(".", ","))));
                    //NodeInsert.Parameters.Add(new SqlParameter("@Node_Rue_Id", StreetKey));
                    NodeInsert.ExecuteNonQuery();
 
                }
 
            }
 
        }

[Graffito]

Je verrai 2 possibilités:
- créer une commande SQL avec quelques milliers d'INSERT à exécuter via la procedure ExecuteNonQuerry().
- utiliser un Dataset (la base de données) et une DataTable avec son InsertCommand, ajouter les enregistrements dans la DataTable et faire un Update du DataSet.

[_PascalC_]

>Existe t'il un moyen d'optimiser l'insertion en base ?

vi on peut ! Sur mon vieux portable de 2003 (PIVm 2.0ghz - 512 Mo RAM), j'ai inséré à l'instant les 15 milions de lignes en 51s dans un SQL Express 2008 (sur la même machine). Bien sûr il faudra rajouter à cela le temps de lecture de ton flux xml...

EDIT> je viens d'essayer ce matin sur une machine récente et ça tombe à environ 8s (SQL Express 2008 là aussi)

Dans ton bout de code, je vois que tu construis l'objet "NpgsqlCommand" à chaque nouvelle ligne à insérer. C'est inutile car la requête en elle même ne change jamais d'une ligne à une autre... Seules les valeurs des paramètres de la requête changent ! Il aurait donc été préferable de faire quelque chose de ce genre :

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
 
NpgsqlCommand NodeInsert = sqlConnex.CreateCommand();
NodeInsert.CommandText = "INSERT INTO (Node_OSM_Id,Node_OSM_Lat,Node_OSM_Lon) VALUES (@Node_OSM_Id,@Node_OSM_Lat,@Node_OSM_Lon)";
NodeInsert.Parameters.Add("@Node_OSM_Id", SqlDbType.Int);
NodeInsert.Parameters.Add("@Node_OSM_Lat", SqlDbType.Decimal);
NodeInsert.Parameters["@Node_OSM_Lat"].Precision = 18;
NodeInsert.Parameters["@Node_OSM_Lat"].Scale = 0;
NodeInsert.Parameters.Add("@Node_OSM_Id", SqlDbType.Decimal);
NodeInsert.Parameters["@Node_OSM_Lon"].Precision = 18;
NodeInsert.Parameters["@Node_OSM_Lon"].Scale = 0;
NodeInsert.Prepare();
 
while (xmtr.Read())
{
	//lecture du flux Xml
	NodeInsert.Parameters[0].Value = 0;
	NodeInsert.Parameters[1].Value = 0;
	NodeInsert.Parameters[2].Value = 0;
	NodeInsert.ExecuteNonQuery();
}

On remarquera au passage l'utilisation de la méthode Prepare() sur l'objet NodeInsert qui permet de d'avoir une version compilée de la requête côté serveur.
On peut aussi gagner en performance ici en utilisant les transactions. C'est pas leur rôle normalement mais suivant ce que tu fais ça peut être une bonne idée... ou non

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
NpgsqlCommand NodeInsert = sqlConnex.CreateCommand();
NodeInsert.Transaction = sqlConnex.BeginTransaction();
NodeInsert.CommandText = "INSERT INTO (Node_OSM_Id,Node_OSM_Lat,Node_OSM_Lon) VALUES (@Node_OSM_Id,@Node_OSM_Lat,@Node_OSM_Lon)";
//...
while (xmtr.Read())
{
	//...
	NodeInsert.ExecuteNonQuery();
}
NodeInsert.Transaction.Commit();

Maintenant, voici mes 2 bouts de codes.
Le premier reprend ce que j'ai dis plus haut :

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
 
using (SqlConnection c = new SqlConnection("data source=portable\\sqlexpress; initial catalog=Test; Integrated security=sspi"))
{
	c.Open();
	using (SqlCommand cmd = c.CreateCommand())
	{
		Stopwatch sw = new Stopwatch();
		sw.Start();
		cmd.Transaction = c.BeginTransaction();
		cmd.CommandText = "INSERT INTO T_Points (ID, Latitude, Longitude) VALUES (@ID, @Latitude, @longitude)";
		cmd.Parameters.Add("@ID", SqlDbType.Int);
		cmd.Parameters.Add("@Latitude", SqlDbType.Decimal);
		cmd.Parameters["@Latitude"].Precision = 18;
		cmd.Parameters["@Latitude"].Scale = 0;
		cmd.Parameters.Add("@Longitude", SqlDbType.Decimal);
		cmd.Parameters["@Longitude"].Precision = 18;
		cmd.Parameters["@Longitude"].Scale = 0;
		cmd.Prepare();
		for (int i = 0; i < 15000000; i++)
		{
			cmd.Parameters[0].Value = i;
			cmd.Parameters[1].Value = 46.1234;
			cmd.Parameters[2].Value = -1.2345;
			cmd.ExecuteNonQuery();
		}
		cmd.Transaction.Commit();
		sw.Stop();
	}
}

... et maintenant le second avec une approche complètement différente (120x plus rapide que la précédente).

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
 
int limit = 500; // a trouver après quelques tests
int count = 15000000;
using (SqlConnection c = new SqlConnection("data source=portable\\sqlexpress; initial catalog=Test; Integrated security=sspi"))
{
	c.Open();
	using (SqlCommand cmd = c.CreateCommand())
	{
		Stopwatch sw = new Stopwatch();
		sw.Start();
		cmd.Transaction = c.BeginTransaction();
		StringBuilder sb = null;
		for (int i = 0; i <= count; i++)
		{
			if (i % limit == 0 || i == count)
			{
				if (i > 0)
				{
					sb.Remove(sb.Length - 7, 7);
					cmd.CommandText = sb.ToString();
					cmd.ExecuteNonQuery();
					if (i == count)
						break;
				}
				sb = new StringBuilder(36 * limit + 47); //Taille max que peut atteindre un SELECT * Nb de select + Taille de l'INSERT 
				sb.Append("INSERT INTO T_Points (ID, Latitude, Longitude) ");
			}
			sb.Append("SELECT 1, 45.12345, -15675473 UNION ");
		}
		cmd.Transaction.Commit();
		sw.Stop();
	}
}

Le principe ici est d'insérer plusieurs lignes en une seule requête INSERT :

INSERT INTO MaTable (Colonne1, Colonne2...)
SELECT Valeur1, Valeur2
UNION
SELECT Valeur1, Valeur2
UNION
SELECT Valeur1, Valeur2

Au delà d'un certain nombre de SELECT les perfs se dégradent. A toi de trouver le juste milieu. Cette syntaxe marche avec SQL Server mais probablement pas avec tous les SGBD. A voir si Postgre a une syntaxe équivalente...
On remarque au passage l'utilisation d'un StringBuilder pour construire la requête. Celui-ci est "dimensionné" dès sa création à une valeur proche du Max de ce qu'il aura à stocker afin qu'il n'est pas besoin de réalouer de temps à autre de la mémoire.

[laedit]

Fastest universal way to insert data using standard ADO.NET constructs : http://sqlite.phxsoftware.com/forums/t/134.aspx.

ça revient en substance à ce qu'à dit PascalC : éviter de recréer la Command qui ne change pas, on va juste changer la requête.
Il est préférable aussi d'utiliser des requêtes paramétrées et bien sûr une transaction.