Optimisation accès BD (insert/update lent)

**Mast3rMind** · 10/08/2007, 16h46

Bonjour,

J'effectue présentement plusieurs requêtes à une base de données et le processus d'upate/insertion de données est TRÈS lent. Je me demandais ce que je fais d'incorrect...

Note: J'ai environ 10 000 data à insérer/updater

Voici en gros ce que fait mon code:

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
 
foreach (CYMEXTDeviceData deviceData in mapDevice.Keys)
{
	Int32 nRowsModified = 0;
 
        String strStatement;
	OleDbCommand cmd;
 
	strStatement = "UPDATE " + DEVICE_TABLE_NAME + " SET " +
                                      "FEATURE_TYPE = '" + deviceData.FeatureType + "', " +
                                      "FEATURE_SUBTYPE = '" + deviceData.FeatureSubType + "', " +
                                      " WHERE DID = '" + deviceData.DID + "'";
 
	cmd = new OleDbCommand(strStatement, connection);
 
	try
	{
		nRowsModified = cmd.ExecuteNonQuery();
	}
	catch (OleDbException ex)
	{
		errors.CreateErrorByParsingString(StringTable.MSG_CANNOT_UPDATE, DEVICE_TABLE_NAME, ex.Message);
                nRowsModified = 0;
	}
}

J'ai lu sur les DataSet / DataTable mais je n'y ai pas compris grand chose... serait-ce la solution?

Merci!

**Mast3rMind** · 10/08/2007, 22h12

En fait, ma question pourrait se reformuler ainsi:

Quelle est la façon la plus rapide pour insérer des milliers de records dans une base de données Oracle, à partir de données stockées en mémoire?

**cinemania** · 10/08/2007, 23h13

Si tu le fait sous oracle si tu souhaite améliorer ton traitement ya deja deux facon defaire

1. prenons déjà ta méthode et améliorons la

dans un premier temps tu change les connexion OleDb par des connexions de types OracleConnection, OracleCommand... qui sont "natives"
plus tu minimise les intermédiaires plus c'est rapide

ensuite, lorsque l'on souhaite exécuter un lot de commandes, on créé une transaction qui va verrouiller l'accès à la table concerné ce qui va également améliorer le principe car temps que la transaction n'estpas finie, elle reste prioritaire (si tu verrouille en isolation maxi => serialized)
toutes les autres qui souhaite y accèder son bloquées pendantle temps que dure la transaction en cours.

Ensuite on va préparer un lot plutot qu'une simple requete.

Pour cela tu prend ta commande SQL et tu fait command.Prepare();
Ca va la préparer et précompiler.

Ensuite tu l'exécute normallement.
Tu devrais remarquer une amélioration d'environ 10% à 20%.

2. Utilisation des DataTable et DataSet.
Alors on commence le principe de la même façon, on commence une transaction en isolation maxi.

ensuite on créé une requete de type insert dans une oraclecommand a laquelle on oublie surtout pas d'affecter la transaction obtenue

ensuite on prend la datatable dans laquelle tuas mis TOUTES les données à "insérer"
dans des datarow suivant un schéma avec des columns et des relations entre et des notions de clés...

Ensuite tu créer un OracleAdapter en fournissant la connexion,
et tului affecte en InsertCommand la commande que tu viens de créer.

Maintenant on va "mapper" les colonnes de la datatable à ta Requête

si ta requete est : insert into truc values (#id, #machin, #bidule)
correspondant à la table (id, machin, bidule)

tu va créer des paramètres (OracleParameter pour chaque paramètre de ta OracleCommand en associant ainsi un nomdeparamètre comme par exemple : #machin, à lacolonne machin.

Une fois tout ce cirque préparatoire fini
tu fait: adapter.Update(dt)
avec adapter l'OracleAdapter
et dt la datatable

Dans ce second scénario tu peux espérer une diminution du temps de 15% à 60%. Pourquoi une telle fourchette ? car elle dépend fortement de ta requete.
de plus Oracle ne sait pas optimiser une requete pour améliorer le plan d'exécution de celle-ci, meme si tu la prépare pour effectuer un lot.

ensuite une fois fini dans les DEUX CAS tu prend ta transaction oracle et tu fait :
tran.Commit();

**Mast3rMind** · 13/08/2007, 14h35

Merci énormément pour ces conseils!

Je les essaie et je vous tiens au courant des résultats.

**Mast3rMind** · 13/08/2007, 16h37

Bon, est-ce que quelqu'un peut valider mon code svp?

Note: J'utilise des objets OleDB plutôt que Oracle car ma connexion peut être de différents types (Access, oracle, etc)

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
 
Boolean bSuccess = true;
 
            // Create transaction
            OleDbTransaction myOleDBTransaction = connection.BeginTransaction();
 
            // Create query
            OleDbCommand cmd = connection.CreateCommand();
 
            DataTable dt = new DataTable("DT_" + CYMEXTDEVICE_TABLE_NAME);
 
            // Ajout des entêtes de colonnes a mon DataTable.
            dt.Columns.Add(new DataColumn("FeatureType", typeof(string)));
            dt.Columns.Add(new DataColumn("FeatureSubType", typeof(string)));
 
            // SQL Query            
            String strStatement = "INSERT INTO " + CYMEXTDEVICE_TABLE_NAME +
                                       " VALUES(" +
                                       ":FeatureType, :FeatureSubType)";
 
            foreach (CYMEXTDeviceData deviceData in mapDevice.Keys)
            {
                DataRow row = dt.NewRow();
                row["FeatureType"] = deviceData.FeatureType;
                row["FeatureSubType"] = deviceData.FeatureSubType;
 
                dt.Rows.Add(row);
            }
 
            OleDbParameter paramFeatureType = new OleDbParameter("FeatureType", OleDbType.VarChar);
            OleDbParameter paramFeatureSubType = new OleDbParameter("FeatureSubType", OleDbType.VarChar);
 
            cmd.Parameters.Add(paramFeatureType);
            cmd.Parameters.Add(paramFeatureSubType);
 
            cmd.CommandText = strStatement;
            cmd.Transaction = myOleDBTransaction;
 
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.InsertCommand = cmd;
 
            adapter.Update(dt);
 
            myOleDBTransaction.Commit();