Rédaction d'un grand nombre de documents (encart en vrac) à consulter in.NET/C#

j'ai trouvé que l'utilisation de DAO d'une manière spécifique est environ 30 fois plus rapide que l'utilisation ADO.NET. I am sharing the code and results in this answer. Comme toile de fond, dans le ci-dessous, le test est d'écrire 100 000 enregistrements d'un tableau avec 20 colonnes.

Un résumé de la technique et le temps - du meilleur au pire:

1. 02.8 secondes: utilisez DAO, utilisez DAO.Field pour se référer aux colonnes du tableau
2. 02.8 secondes: Ecrire à un fichier texte, Utiliser L'automatisation pour importer le texte dans L'accès
3. 11.0 secondes: utilisez DAO, utilisez l'index des colonnes pour se référer au tableau colonnes.
4. 17.0 secondes: Utiliser DAO, reportez-vous à la colonne par le nom de
5. 79.0 secondes: utiliser ADO.NET, générer insérer des instructions pour chaque ligne 1519220920"
6. 86.0 seconds: Use ADO.NET, utilisez Datatatable to an Dataaadapter for "batch" insert

comme arrière-plan, il m'arrive de devoir analyser de grandes quantités de données, et je trouve que L'accès est la meilleure plate-forme. L'analyse implique de nombreuses requêtes, et souvent beaucoup de code VBA.

pour diverses raisons, je voulais utiliser C# au lieu de VBA. La façon typique est d'utiliser OleDB pour se connecter à Access. J'ai utilisé un OleDbDataReader pour récupérer des millions de disques, et ça a très bien marché. Mais quand la sortie des résultats à une table, il a fallu un long, long temps. Plus d'une heure.

tout d'abord, nous allons discuter des deux façons typiques d'écrire des dossiers à accéder à partir de C#. Les deux voies impliquent OleDB et ADO.NET. La première est de générer des INSERT statements un à la fois, et de les exécuter, en prenant 79 secondes pour les 100 000 enregistrements. Le code est:

public static double TestADONET_Insert_TransferToAccess()
{
  StringBuilder names = new StringBuilder();
  for (int k = 0; k < 20; k++)
  {
    string fieldName = "Field" + (k + 1).ToString();
    if (k > 0)
    {
      names.Append(",");
    }
    names.Append(fieldName);
  }

  DateTime start = DateTime.Now;
  using (OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.AccessDB))
  {
    conn.Open();
    OleDbCommand cmd = new OleDbCommand();
    cmd.Connection = conn;

    cmd.CommandText = "DELETE FROM TEMP";
    int numRowsDeleted = cmd.ExecuteNonQuery();
    Console.WriteLine("Deleted {0} rows from TEMP", numRowsDeleted);

    for (int i = 0; i < 100000; i++)
    {
      StringBuilder insertSQL = new StringBuilder("INSERT INTO TEMP (")
        .Append(names)
        .Append(") VALUES (");

      for (int k = 0; k < 19; k++)
      {
        insertSQL.Append(i + k).Append(",");
      }
      insertSQL.Append(i + 19).Append(")");
      cmd.CommandText = insertSQL.ToString();
      cmd.ExecuteNonQuery();
    }
    cmd.Dispose();
  }
  double elapsedTimeInSeconds = DateTime.Now.Subtract(start).TotalSeconds;
  Console.WriteLine("Append took {0} seconds", elapsedTimeInSeconds);
  return elapsedTimeInSeconds;
}

notez que je n'ai trouvé aucune méthode dans Access qui permette un insert en vrac.

j'avais alors pensé que peut-être l'utilisation d'une table de données avec un adaptateur de données serait utile. Surtout depuis que j'ai pensé que je pouvais faire des inserts par lots en utilisant la propriété UpdateBatchSize d'un adaptateur de données. Cependant, apparemment seulement SQL Server et Oracle soutiennent cela, et L'accès ne le fait pas. Et ça a pris le plus long temps de 86 secondes. Le code que j'ai utilisé était:

public static double TestADONET_DataTable_TransferToAccess()
{
  StringBuilder names = new StringBuilder();
  StringBuilder values = new StringBuilder();
  DataTable dt = new DataTable("TEMP");
  for (int k = 0; k < 20; k++)
  {
    string fieldName = "Field" + (k + 1).ToString();
    dt.Columns.Add(fieldName, typeof(int));
    if (k > 0)
    {
      names.Append(",");
      values.Append(",");
    }
    names.Append(fieldName);
    values.Append("@" + fieldName);
  }

  DateTime start = DateTime.Now;
  OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.AccessDB);
  conn.Open();
  OleDbCommand cmd = new OleDbCommand();
  cmd.Connection = conn;

  cmd.CommandText = "DELETE FROM TEMP";
  int numRowsDeleted = cmd.ExecuteNonQuery();
  Console.WriteLine("Deleted {0} rows from TEMP", numRowsDeleted);

  OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM TEMP", conn);

  da.InsertCommand = new OleDbCommand("INSERT INTO TEMP (" + names.ToString() + ") VALUES (" + values.ToString() + ")");
  for (int k = 0; k < 20; k++)
  {
    string fieldName = "Field" + (k + 1).ToString();
    da.InsertCommand.Parameters.Add("@" + fieldName, OleDbType.Integer, 4, fieldName);
  }
  da.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
  da.InsertCommand.Connection = conn;
  //da.UpdateBatchSize = 0;

  for (int i = 0; i < 100000; i++)
  {
    DataRow dr = dt.NewRow();
    for (int k = 0; k < 20; k++)
    {
      dr["Field" + (k + 1).ToString()] = i + k;
    }
    dt.Rows.Add(dr);
  }
  da.Update(dt);
  conn.Close();

  double elapsedTimeInSeconds = DateTime.Now.Subtract(start).TotalSeconds;
  Console.WriteLine("Append took {0} seconds", elapsedTimeInSeconds);
  return elapsedTimeInSeconds;
}

puis j'ai essayé des méthodes non standard. D'abord, j'ai écrit dans un fichier texte, puis J'ai utilisé L'automatisation pour importer ça. Ce fut rapide 2,8 secondes et à égalité pour la première place. Mais je considère cela fragile pour un certain nombre de raisons: dépasser les champs de date est délicat. J'ai dû les formater spécialement ( someDate.ToString("yyyy-MM-dd HH:mm") ), puis établir une "spécification d'importation" spéciale qui code dans ce format. La spécification d'importation devait également avoir la "citation" délimiteur défini droit. Dans l'exemple ci-dessous, avec seulement des champs entiers, il n'y avait pas besoin de spécification d'importation.

les fichiers textes sont également fragiles pour l '"internationalisation" où il y a une utilisation de virgule pour les séparateurs décimaux, différents formats de date, possible l'utilisation d'unicode.

notez que le premier enregistrement contient les noms de champs de sorte que l'ordre de la colonne ne dépend pas de la table, et que nous avons utilisé L'automatisation pour faire l'importation réelle du fichier texte.

public static double TestTextTransferToAccess()
{
  StringBuilder names = new StringBuilder();
  for (int k = 0; k < 20; k++)
  {
    string fieldName = "Field" + (k + 1).ToString();
    if (k > 0)
    {
      names.Append(",");
    }
    names.Append(fieldName);
  }

  DateTime start = DateTime.Now;
  StreamWriter sw = new StreamWriter(Properties.Settings.Default.TEMPPathLocation);

  sw.WriteLine(names);
  for (int i = 0; i < 100000; i++)
  {
    for (int k = 0; k < 19; k++)
    {
      sw.Write(i + k);
      sw.Write(",");
    }
    sw.WriteLine(i + 19);
  }
  sw.Close();

  ACCESS.Application accApplication = new ACCESS.Application();
  string databaseName = Properties.Settings.Default.AccessDB
    .Split(new char[] { ';' }).First(s => s.StartsWith("Data Source=")).Substring(12);

  accApplication.OpenCurrentDatabase(databaseName, false, "");
  accApplication.DoCmd.RunSQL("DELETE FROM TEMP");
  accApplication.DoCmd.TransferText(TransferType: ACCESS.AcTextTransferType.acImportDelim,
  TableName: "TEMP",
  FileName: Properties.Settings.Default.TEMPPathLocation,
  HasFieldNames: true);
  accApplication.CloseCurrentDatabase();
  accApplication.Quit();
  accApplication = null;

  double elapsedTimeInSeconds = DateTime.Now.Subtract(start).TotalSeconds;
  Console.WriteLine("Append took {0} seconds", elapsedTimeInSeconds);
  return elapsedTimeInSeconds;
}

enfin, j'ai essayé DAO. Beaucoup de sites là-bas donnent d'énormes avertissements au sujet de l'utilisation de DAO. Cependant, il s'avère que C'est tout simplement la meilleure façon d'interagir entre Access et .NET, surtout lorsque vous avez besoin d'écrire un grand nombre de documents. En outre, il donne accès à toutes les propriétés d'une table. J'ai lu quelque part qu'il est plus facile de programmer des transactions en utilisant DAO au lieu de ADO.NET.

notez qu'il y a plusieurs lignes de code qui sont commentées. Ils seront bientôt expliqués.

public static double TestDAOTransferToAccess()
{

  string databaseName = Properties.Settings.Default.AccessDB
    .Split(new char[] { ';' }).First(s => s.StartsWith("Data Source=")).Substring(12);

  DateTime start = DateTime.Now;
  DAO.DBEngine dbEngine = new DAO.DBEngine();
  DAO.Database db = dbEngine.OpenDatabase(databaseName);

  db.Execute("DELETE FROM TEMP");

  DAO.Recordset rs = db.OpenRecordset("TEMP");

  DAO.Field[] myFields = new DAO.Field[20];
  for (int k = 0; k < 20; k++) myFields[k] = rs.Fields["Field" + (k + 1).ToString()];

  //dbEngine.BeginTrans();
  for (int i = 0; i < 100000; i++)
  {
    rs.AddNew();
    for (int k = 0; k < 20; k++)
    {
      //rs.Fields[k].Value = i + k;
      myFields[k].Value = i + k;
      //rs.Fields["Field" + (k + 1).ToString()].Value = i + k;
    }
    rs.Update();
    //if (0 == i % 5000)
    //{
      //dbEngine.CommitTrans();
      //dbEngine.BeginTrans();
    //}
  }
  //dbEngine.CommitTrans();
  rs.Close();
  db.Close();

  double elapsedTimeInSeconds = DateTime.Now.Subtract(start).TotalSeconds;
  Console.WriteLine("Append took {0} seconds", elapsedTimeInSeconds);
  return elapsedTimeInSeconds;
}

dans ce code, nous avons créé DAO.Variables de champ pour chaque colonne ( myFields[k] ) et les a ensuite utilisées. Ça a pris 2,8 secondes. Il est également possible d'accéder directement aux champs qui se trouvent dans la ligne commentée rs.Fields["Field" + (k + 1).ToString()].Value = i + k; , ce qui fait passer le temps à 17 secondes. Envelopper le code dans une transaction (voir les lignes commentées) a fait tomber cela à 14 secondes. En utilisant un index entier rs.Fields[k].Value = i + k; droppped cela à 11 secondes. Utiliser le DAO.Le champ myFields[k] ) et une transaction prenait en fait plus de temps, ce qui augmentait le temps à 3,1 secondes.

enfin, pour être complet, tout ce code était dans une classe statique simple, et les déclarations using sont:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ACCESS = Microsoft.Office.Interop.Access; // USED ONLY FOR THE TEXT FILE METHOD
using DAO = Microsoft.Office.Interop.Access.Dao; // USED ONLY FOR THE DAO METHOD
using System.Data; // USED ONLY FOR THE ADO.NET/DataTable METHOD
using System.Data.OleDb; // USED FOR BOTH ADO.NET METHODS
using System.IO;  // USED ONLY FOR THE TEXT FILE METHOD