Optimisation accès BD (insert/update lent)

Version imprimable

[ADO]Optimisation accès BD (insert/update lent)

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:

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! :king:

10/08/2007, 22h12
Mast3rMind

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?

:yaisse2:
10/08/2007, 23h13
cinemania

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();
13/08/2007, 14h35
Mast3rMind

Merci énormément pour ces conseils!

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

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:

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();

13/08/2007, 17h50
Mast3rMind

Quand j'exécute j'obtiens plein d'erreurs du genre:

Code:

Parameter[0] '' has no default value

Je dois mettre des valeurs par défaut à mes paramètres? Je ne comprends pas...
13/08/2007, 23h51
cinemania
normal.

tu n'a pas mappé tes paramètres à une colonne de la datatable là.
Le mappage se fait dans le constructeur.
Pour que ton paramètre soit mappé à la table il faut le construire comme suite :
Code:

1 2 OleDbParameter odp = new OleDbParameter("FeatureType", OleDbType.VarChar, 50, "FeatureType");
Voila à quoi doit ressembler le constructeur en question... il faut préciser la longueur de base du type s'il en nécessite une, et le quatrième paramètre est le nom de la colonne de la table à mapper... oh miracle.
Et comme par hasard, une fois le paramètre mappé à une colonne.. a pu besoin de valeur par défaut, ou meme de valeur.

Ton code tel qu'il est écrit nécessite une valeur par défaut car il ne contient pas de mappage à une colonne et a aucun moment tu ne fait d'affectation de la valeur courante du paramètre, doù tes messages d'erreur en pagaille.

La facon la plus optimale est de créé une StoredProcedure et de lui passé du xml en parametre.
Coté SP, tu lit ton XML et tu insert tes valeurs
C'est le plus performant.

Pour ton XML, tu peux sérialiser tes données pour l'obtenir!

Voici un exemple en sqlserver

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
DECLARE @XML AS NVARCHAR(max)
SET @XML = 
'<Voitures>
<Voiture ID=''2'' ConstructionDate=''5/1/2007'' />
<Voiture ID=''3'' ConstructionDate=''5/1/2007'' />
<Voiture ID=''4'' ConstructionDate=''5/1/2007'' />
<Voiture ID=''5'' ConstructionDate=''5/1/2007'' />
</Voitures>'
 
DECLARE @DocHandle AS INT
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XML
 
 
UPDATE Voiture SET ConstructionDate = x.ConstructionDate FROM Voiture
INNER JOIN OPENXML(@DocHandle, '//Voitures', 1) WITH (ID INT '@ID', ConstructionDate DATETIME '@ConstructionDate') X
 
INSERT INTO Voitures 
	SELECT * FROM OPENXML(@DocHandle, '//Voitures', 1) WITH (ID INT '@ID', ConstructionDate DATETIME '@ConstructionDate')
 
EXEC sp_xml_removedocument @DocHandle

14/08/2007, 10h14
bilb0t

Attetion qu'il n'utilise pas toujours Oracle...

Citation:

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

Et que je suis pas sur que toutes les DB accepte du flux XML en entrée...

Et je suis pas trop sur que le parsing des string XML est si performant dans un DB...
14/08/2007, 13h12
cinemania

meme pour sqlserver, le flux xml pose un probleme de taille...

nombre de serveurs sql server de nos jours en productions sont encore sous la version 2000. Si je regarde dans le groupe la grande majorité à part les serveurs de dev sont en 2000. Chez un client tous le sserveurs de prod sont en 2000.
Or XML n'est pris en charge qu'a partir de la version 2005 de SQL Server.
Toutes les versions d'oracle ne gère pas non plus le XML.

en plus NVARCHAR comme VARCHAR ne peut contenir que jusque 4000 caractères, sous sqlserver, ce qui est très loin de ses besoins, et problème... une storedproc n'accepte pas de paramètre TEXT ou NTEXT en entrée.
14/08/2007, 13h30
shwin

nvarchar(max) peut contenir environ 4500 char mais tu peux lui en passé plus que ca. J'ai fait le test.

La lecture du xml est extremement performant dans sql. C'est lit en Xpath. C'est vraiment mieux que de faire 10000 appel d'insert. Il est mieux d'inséré 10000 en 1 query que 10000 query.

Je connais pas les autre moteur sql. Donc c'est libre d'utiliser le xml dans certain cas
14/08/2007, 13h31
bilb0t

Dans Sql Serveur, les varchar et NVarchar sont limités à 8000

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
	SqlCommand command = new SqlCommand();
			command.CommandType = CommandType.StoredProcedure;
			command.CommandText = "pTest";
			command.Connection = conn;
			List<Voiture> voitures = new List<Voiture>();
 
			for(int i=0; i<10000;i++)
				voitures.Add(new Voiture(i, "Berline"));
 
				// Convert list to XML
			XmlSerializer serializer = new XmlSerializer(voitures.GetType());
			StringBuilder sb = new StringBuilder();
			XmlWriter writer = XmlTextWriter.Create(sb);
			serializer.Serialize(writer, voitures);
			writer.Flush();
 
			command.Parameters.Add("@VoitureIds", SqlDbType.NVarChar).Value = sb.ToString();
			conn.Open();
			command.ExecuteNonQuery();
			conn.Close();

sb.ToString().Length = 559061
Pour inserer 10000 ligne ca prend 2 secondes

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
alter PROCEDURE [dbo].[pTest]
	@VoitureIds AS NVARCHAR(MAX)
AS
BEGIN
DECLARE @table AS TABLE(VehiculeId INT, Modele NVARCHAR(30))
 
DECLARE @DocHandle AS INT
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @VoitureIds
 
INSERT INTO @table 
	SELECT * FROM OPENXML(@DocHandle, '//Voiture', 1) WITH (VehiculeId INT '@ID', Modele NVARCHAR(30) '@Modele')
 
EXEC sp_xml_removedocument @DocHandle 
END

Donc on ne peut pas dire que c'est lent et que sql est non performant en xml. C'est tout le contraire.

Wow merci pour tous vos conseils, j'en apprends beaucoup!

Sinon, je préfère utiliser des Dataset pour modifier ma table, pour des raisons de compatibilité entre databases (access, oracle, sql server) et peu importe les versions de celles-ci.

Donc, j'ai modifié un peu mon approche.

Voici ce que je fais:

1- Je récupère ma table dans un dataset à l'aide d'un dataadapter
2- Je définit une clé primaire
3- J'édite certaines lignes du dataset si je les trouve
4- Si je ne les trouve pas je les ajoute

Mon problème est que lorsque j'édite une ligne, aucun changement n'est pris en compte... je me demande ce que je fais d'incorrect...

Quelqu'un sait pourquoi?

Code:

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
Boolean bSuccess = true;
 
            DataSet dataSet = new DataSet("AllDevices");
 
            String strSelectAll = "SELECT * FROM " + TABLE_NAME;
 
            OleDbDataAdapter daDevices = new OleDbDataAdapter(strSelectAll, connection);
 
            // Get all the data
            daDevices.Fill(dataSet, "Devices");
 
            // Set Primary Key
            DataColumn[] dcPk = new DataColumn[1];
            dcPk[0] = dataSet.Tables["Devices"].Columns["DID"];
            dataSet.Tables["Devices"].PrimaryKey = dcPk;
 
            // Set Default Sort
            dataSet.Tables["Devices"].DefaultView.Sort = "DID";
 
            // Update all the desired rows in the datatable
            foreach (CYMEXTDeviceData deviceData in mapDevice.Keys)
            {
                DataRow drFound;
 
                drFound = dataSet.Tables["Devices"].Rows.Find(deviceData.DID);
 
                int i;
                // If row exists, update the data
                if(drFound != null) 
                {
                    drFound.BeginEdit();
 
                    i = 0;
                    drFound.ItemArray[i] = deviceData.FeatureType;
                    drFound.ItemArray[i++] = deviceData.FeatureSubType;
                    drFound.ItemArray[i++] = deviceData.State;
 
                    drFound.EndEdit();
                }
                else // Create new row
                {
                    i = 0;
                    object[] rowVals = new object[3];
                    rowVals[i] = deviceData.FeatureType;
                    rowVals[i++] = deviceData.FeatureSubType;
                    rowVals[i++] = deviceData.State;
 
                    dataSet.Tables["Devices"].Rows.Add(rowVals);
                }
            }
 
            daDevices.Update(dataSet);
 
            return true;

14/08/2007, 16h31
Mast3rMind

Ok, j'ai trouvé. Il faut faire

Code:

drFound[i++] = deviceData.FeatureType;

au lieu de

Code:

drFound.ItemArray[i] = deviceData.FeatureType;

Mais ça plante à la ligne

Code:

daDevices.Update(dataSet, "Devices")

J'obtiens l'erreur suivante: Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

Je ne sais pas comment régler ça...

Help plz :roll:
15/08/2007, 23h03
cinemania

logique, jusqu'ici tu as défini une commande pour inserer les entrées qu'il ne trouve pas... mais tun'a pas créé de commande pour faire les update.
Or quand tu fait varier une ligne, elle existe deja, elle est juste différente de la base... il font donc un UPDATE !

C'est exactement le meme principe que pour l'insert, sauf que tuva mettre une commande avec update et les différents Set et un where sur la clé bien sure... dans tous les cas n'oublie pas de mapper les paramètres à des colonnes.

En ce qui concerne le XML, je me répete car beaucoup n'ont visiblement pas compris... SQL Server est à ce jour le plus performant en matière de traitement XML, malheureusement il ne supporte le XML qu'a partir de la version 2005 ce qui représente moins de 20% des serveurs SQL Server en production de nos jours.

Pour les NVARCHAR et VARCHAR... cela dépend de la version la taille limite...
sous sql server 2000, ils sont limités à 4000 pour NVARCHAR et 8000 pour VARCHAR (caractères non unicodes). Sous sql server 2005, l'index de taille est sur 64 bits ce qui signifie que varchar(max) peut héberger jusque 2^64. et nvarchar 2^63 caractères unicodes.
Vous faites vos tests sur 2005. Pensez que les serveurs de prod pratiquement partout sont encore en 2000 et que celui-ci est très limité par rapport à 2005.
A titre indicatif, plutot que d'utiliser le type varchar... SQL Server 2005 dispose du type natif SQL: XML.
16/08/2007, 14h15
Mast3rMind

Merci à tous pour vos réponses!

J'ai finalement réussi à faire fonctionner le tout, et j'obtiens des gains de performance de 53% par rapport à la version sans DataTable/DataAdapter ! Héhé trop cool :king:

A++