Zapis dużej liczby rekordów (wkładka zbiorcza) w celu uzyskania dostępu in.NET/C#

Odkryłem, że używanie DAO w określony sposób jest około 30 razy szybsze niż używanie ADO.NET. dzielę się kodem i wyniki w tej odpowiedzi. Jako tło, w poniższym teście jest wypisanie 100 000 rekordów tabeli z 20 kolumnami.

Podsumowanie techniki i czasów - od najlepszego do gorszego:

1. 02.8 sekundy: użyj DAO, użyj DAO.Field's, aby odnieść się do kolumn tabeli
2. 02.8 sekundy: zapis do pliku tekstowego, użyj Automatyzacja importu tekstu do Access
3. 11.0 sekund: użyj DAO, użyj indeksu kolumny, aby odnieść się do kolumn tabeli.
4. 17.0 sekund: użyj DAO, odwołaj się do kolumny po nazwie
5. 79.0 sekund: użyj ADO.NET, generowanie poleceń INSERT dla każdego wiersza
6. 86.0 sekund: użyj ADO.NET, użyj DataTable do DataAdapter dla" wsadowej " insert

Jako tło, czasami muszę wykonać analizę dość duże ilości danych i uważam, że Access jest najlepszą platformą. Analiza obejmuje wiele zapytań, a często dużo kodu VBA.

Z różnych powodów chciałem użyć C# zamiast VBA. Typowym sposobem jest użycie OleDB do połączenia z Access. Użyłem OleDbDataReader, aby zdobyć miliony płyt, i to działało całkiem dobrze. Ale wysyłanie wyników do tabeli zajęło dużo, dużo czasu. Ponad godzinę.

Najpierw omówmy dwa typowe sposoby zapisu rekordów do Dostęp z C#. Oba sposoby obejmują OleDB i ADO.NET. pierwszym z nich jest generowanie poleceń INSERT pojedynczo i ich wykonywanie, co zajmuje 79 sekund dla 100 000 rekordów. Kod to:

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;
}

Zauważ, że nie znalazłem w Access metody pozwalającej na masową wstawkę.

Pomyślałem wtedy, że może użycie tabeli danych z adapterem danych okaże się przydatne. Zwłaszcza, że pomyślałem, że mogę zrobić wsadowe wstawki używając właściwości UpdateBatchSize adaptera danych. Jednak najwyraźniej tylko SQL Server i Oracle to obsługują, A Access nie. I trwało to najdłużej 86 sekund. Kod, którego użyłem to:

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;
}

Potem próbowałem niestandardowych sposobów. Najpierw napisałem do pliku tekstowego, a następnie zaimportowałem go za pomocą automatyzacji. Był szybki-2,8 sekundy-i zremisował na pierwszym miejscu. Ale uważam to za delikatne z wielu powodów: Wyprowadzanie pól daty jest trudne. Musiałem sformatować je specjalnie (someDate.ToString("yyyy-MM-dd HH:mm")), a następnie ustawić specjalną " specyfikację importu" koduje w tym formacie. Specyfikacja importu również musiała mieć ustawiony ogranicznik" quote". W poniższym przykładzie, tylko z polami integer, nie było potrzeby wprowadzania specyfikacji importu.

Pliki Tekstowe są również kruche dla" internacjonalizacji", gdzie istnieje użycie przecinków dla separatorów dziesiętnych, różnych formatów dat, możliwe użycie unicode.

Zauważ, że pierwszy rekord zawiera nazwy pól, dzięki czemu kolejność kolumn nie jest zależna od tabeli, i że użyliśmy automatyzacji do rzeczywistego importu pliku tekstowego.

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;
}

Zawiadomienie że istnieje kilka linii kodu, które są komentowane. Wkrótce zostaną wyjaśnione.

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;
}

W tym kodzie stworzyliśmy DAO.Zmienne pola dla każdej kolumny (myFields[k]), a następnie wykorzystywał je. Trwało to 2,8 sekundy. Alternatywnie, można było bezpośrednio uzyskać dostęp do tych pól, które znajdują się w linii komentarza rs.Fields["Field" + (k + 1).ToString()].Value = i + k;, co wydłużyło czas do 17 sekund. Zawijanie kodu w transakcję (patrz linie komentowane) zmniejszyło to do 14 sekund. Użycie indeksu integer rs.Fields[k].Value = i + k; zmniejszyło to do 11 sekund. Używam DAO.Field (myFields[k]) i transakcja faktycznie trwała dłużej, wydłużając czas do 3,1 sekundy.

Wreszcie, dla kompletności, cały ten kod był w prostej statycznej klasie, a using instrukcje to:

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