Найшвидший спосіб оновити (заповнити) 1000000 записів у базу даних за допомогою .NET

Я використовую цей код, щоб вставити 1 мільйон записів у порожню таблицю бази даних. Так, без особливого коду я почну з того моменту, коли я вже взаємодіяла з даними, і читаю схему в DataTable

Тому:

DataTable returnedDtViaLocalDbV11 = DtSqlLocalDb.GetDtViaConName(strConnName, queryStr, strReturnedDtName);

І тепер, коли ми маємо returnDtViaLocalDbV11 , можна створити новий DataTable , щоб стати клоном таблиці вихідної бази даних:

DataTable NewDtForBlkInsert = returnedDtViaLocalDbV11.Clone();

Stopwatch SwSqlMdfLocalDb11 = Stopwatch.StartNew();
NewDtForBlkInsert.BeginLoadData();

for (int i = 0; i < 1000000; i++)
{
   NewDtForBlkInsert.LoadDataRow(new object[] { null, "NewShipperCompanyName"+i.ToString(), "NewShipperPhone" }, false);
}
NewDtForBlkInsert.EndLoadData();

DBRCL_SET.UpdateDBWithNewDtUsingSQLBulkCopy(NewDtForBlkInsert, tblClients._TblName, strConnName);

SwSqlMdfLocalDb11.Stop();

var ResSqlMdfLocalDbv11_0 = SwSqlMdfLocalDb11.ElapsedMilliseconds;

Цей код заповнює 1 мільйон записів до вбудованої бази даних SQL (localDb) у 5200ms . Решта коду - це просто реалізація bulkCopy, але я все одно напишу його.

 public string UpdateDBWithNewDtUsingSQLBulkCopy(DataTable TheLocalDtToPush, string TheOnlineSQLTableName, string WebConfigConName)
 {
    //Open a connection to the database. 
    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[WebConfigConName].ConnectionString))
    {
       connection.Open();

      //Perform an initial count on the destination table.
       SqlCommand commandRowCount = new SqlCommand("SELECT COUNT(*) FROM "+TheOnlineSQLTableName +";", connection);
       long countStart = System.Convert.ToInt32(commandRowCount.ExecuteScalar());

       var nl = "\r\n";
       string retStrReport = "";
       retStrReport = string.Concat(string.Format("Starting row count = {0}", countStart), nl);
       retStrReport += string.Concat("==================================================", nl);
      //Create a table with some rows. 
       //DataTable newCustomers = TheLocalDtToPush;

      //Create the SqlBulkCopy object.  
      //Note that the column positions in the source DataTable  
      //match the column positions in the destination table so  
      //there is no need to map columns.  
       using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
       {
          bulkCopy.DestinationTableName = TheOnlineSQLTableName;

          try
          {
            //Write from the source to the destination.
             for (int colIndex = 0; colIndex < TheLocalDtToPush.Columns.Count; colIndex++)
             {
                bulkCopy.ColumnMappings.Add(colIndex, colIndex);
             }
             bulkCopy.WriteToServer(TheLocalDtToPush);
          }

          catch (Exception ex)
          {
             Console.WriteLine(ex.Message);
          }
       }

      //Perform a final count on the destination  
      //table to see how many rows were added. 
       long countEnd = System.Convert.ToInt32(
       commandRowCount.ExecuteScalar());

       retStrReport += string.Concat("Ending row count = ", countEnd, nl);
       retStrReport += string.Concat("==================================================", nl);
       retStrReport += string.Concat((countEnd - countStart)," rows were added.", nl);
       retStrReport += string.Concat("New Customers Was updated successfully", nl, "END OF PROCESS !");
       //Console.ReadLine();
       return retStrReport;
   }
}

Trying it via a connection to SQL server was around 7000ms(at best) & ~7700ms average. Also via a random kv nosql database took around 40 sec (really I did not even keep records of it as it passed over the x2 of sql variants). So... is there a faster way than what I was testing in my code?

Edit

Я використовую win7 x64 8gb ram і найважливішим я повинен думати (як i5 3ghz) не так вже й великий x3 500Gb Wd на Raid-0 робить роботу ще кращою але я просто кажу, якщо ви будете перевіряти на вашому комп'ютері хоч і порівняйте його з будь-яким іншим методом у вашій конфігурації

10
Пропустіть накладні витрати на DataTable. Якщо потрібно використовувати DataTable, то чому б не використовувати існуючу функцію msdn.microsoft.com/en -us/library/hh485668.aspx ?
додано Автор paparazzo, джерело
Нічого, я бачу, що ви використовуєте цей метод. Проте я б намагався не використовувати DataTable взагалі.
додано Автор paparazzo, джерело
Ви вважаєте, що єдиним способом введення даних є DataTable?
додано Автор paparazzo, джерело
Яку частину DataTable накладні незрозумілі? Просто подивіться на кількість підтримуваних методів і властивостей.
додано Автор paparazzo, джерело
"NoSQL" не є конкретним механізмом бази даних або механізмом зберігання даних. Який двигун?
додано Автор Dark Falcon, джерело
Я думав те ж саме. Я тільки тестував цей же варіант для деяких ETL роботи я повинен зробити - на моєму I7, 16 Гб оперативної пам'яті і SSD я зміг отримати 10 000 000 записів, щоб вставити приблизно за 65 секунд, щоб ці цифри здаються досить точними.
додано Автор tsells, джерело
7 секунд на мільйон записів, не бачачи проблеми.
додано Автор CodeMonkeyForHire, джерело
Замість scalarReader - Використовуйте лише читач даних вперед - SqlDataReader myReader = myCommand.ExecuteReader ();
додано Автор Michael Thamm, джерело
Так, зробіть Список <�Словник > таблиця; ... і знову перевірте? ви думаєте, що це менше ресурсів, а потім відправити його до bulk.Copy (що тут?)
додано Автор Avia Afer, джерело
@Blam розпочався з словника , і цей клас, який ви, напевно, знаєте, може взаємодіяти з будь-якими базами даних, але мені сподобалася таблиця даних (передачі з автоматичним перетворенням), хоча я з задоволенням побачу, що Ви б зробили для покращення
додано Автор Avia Afer, джерело
@Blam деяка автоматизація ... не декларує типи даних у кожному стовпці і такі, так що ви пропонуєте використовувати словники або хеш-таблиць? Я маю на увазі я дійсно не те, що великий винахідник, просто тестування все, що я бачу, що може зберегти більше, якщо його близько 20% або більше, можливо, це буде добре тестувати теж
додано Автор Avia Afer, джерело

8 Відповіді

Ви пробували SSIS? Я ніколи не писав пакунок SSIS з підключенням loacldb, але це саме той вид діяльності, який SSIS повинен добре підходити.

Якщо джерелом даних є SQL Server, іншою ідеєю буде створення пов'язаного сервера. Не впевнений, що це буде працювати з localdb. Якщо ви можете налаштувати пов'язаний сервер, ви можете обійти всі C# разом і завантажити дані за допомогою оператора INSERT .. SELECT ... FROM ... SQL.

2
додано
У Visual Studio вбудований візуальний конструктор для SSIS. Вам потрібно буде провести невелике дослідження, але для переміщення даних в об'ємі немає нічого подібного з точки зору продуктивності.
додано Автор Mark Micallef, джерело
Якщо я читаю ваш код правильно, ви заповнюєте дані з вихідної бази даних, а потім завантажуєте їх у пункт призначення. Я думаю, з SSIS ви можете пропустити DataTable і насос даних безпосередньо в пункт призначення. SSIS (служби інтеграції SQL Server) - це платформа інтеграції даних, яка постачається разом із SQL Server.
додано Автор Mike Henderson, джерело
Ви могли б бути більш конкретними про те, як би ви запропонували реалізацію Ssis з моїм кодом, це код сервера, це як stroed procidure або як він працює, так що ви прийшли до ідеї це допомагає скоротити завдання, зроблені C #
додано Автор Avia Afer, джерело
звичайно, завдяки Майку .. я буду 'досліджувати' підхід SSIS, я подивився на неї з першого погляду, хоча йому потрібно більше часу, витраченого (більше 2 хвилин (:), щоб зрозуміти концепт і uscase і т.д. Я не поінформований про його існування дотепер я переконуюсь я принаймні міг зрозуміти що це
додано Автор Avia Afer, джерело

Видалити цикл ... У SQL спробуйте зробити таблицю з 1 мільйонами рядків ... і залишити приєднатися до неї використовувати це для вставки/вибору даних

1
додано

Спробуйте надіслати його, не зберігаючи його в даних.

See the example at the end of this post, that allows you to do it with an enumerator http://www.developerfusion.com/article/122498/using-sqlbulkcopy-for-high-performance-inserts/

0
додано
спасибі, я буду дивитися в цьому, він впевнений, виглядає цікавим варіантом я дам йому тест, щоб перевірити, скільки часу, зберегти його отримує над datatable, він також використовує datareader проти набору даних ... хворий отримати його скоро я сподіваюся (
додано Автор Avia Afer, джерело

If this is a new project I recommend you to use Entity Framework. In this case you can create a List<> with an object with all the data you need and then simply add it entirely to the corresponding table. This way you are quickly geting the needed data and then sending it to the database at once.

0
додано

Якщо ви просто створюєте безглузді дані, створіть збережену процедуру і просто викликайте її через .net

Якщо ви передаєте справжні дані, знову передача їх до збереженого процесу буде швидше, але вам краще відкинути таблицю і відтворити її з даними.

Якщо вставити один рядок за один раз, це займе більше часу, ніж вставляти його одночасно. Це займе ще довше, якщо ви будете писати індекси.

0
додано

Ви можете використовувати Dapper.NET . Dapper - це мікро-ORM, виконує запит і відображає результати в сильно типізований список. Об'єктно-реляційне відображення (ORM, O/RM, і O/R відображення) в комп'ютерному програмному забезпеченні є технікою програмування для перетворення даних між несумісними системами типу в об'єктно-орієнтованих мовах програмування. Це створює, по суті, "базу даних віртуальних об'єктів", яка може використовуватися з мови програмування

Докладніше:

check out https://code.google.com/p/dapper-dot-net/

Репозиторій GitHub: https://github.com/SamSaffron/dapper-dot-net Сподіваюся, що це допоможе ..

0
додано

Створіть єдиний XML-файл для всіх рядків, які потрібно зберегти в базі даних. Передайте цю збережену процедуру xml в SQL і збережіть всі записи в одному виклику. Але ваша збережена процедура повинна бути написана так, щоб вона могла читати всі прочитані, а потім вставити в таблицю.

0
додано
Я не думаю, що це буде швидше. Більш того, робота з xml і файлами набагато повільніше, ніж просто робота з базою даних.
додано Автор Alejandro del Río, джерело

Я згоден з Mike на SSIS, але це моє не відповідає вашому середовищі, однак для ETL процесів, які включають виклики крос-сервера і загальні процеси потоку даних це великий вбудований інструмент і високо інтегрований.

З 1 мільйонами рядків ви, ймовірно, доведеться робити об'ємну вставку. Залежно від розміру рядка ви дійсно не зможете використовувати збережену процедуру, якщо ви не зробили цього в пачках. Файли даних заповнюють пам'ять досить швидко, знову ж таки залежно від розміру рядка. Ви можете зробити збережену процедуру і мати, що взяти тип таблиці і викликати, що кожен X число рядків, але чому б ми це зробили, коли у вас вже є краще, більш масштабоване рішення. У наступному році цей мільйон рядків може становити 50 мільйонів.

Я використовував SSIS трохи, і якщо це організаційне пристосування, я б запропонував подивитися на нього, але це не буде одноразовою відповіддю.

0
додано
var chat = new Chat();
var chat = new Chat();
642 учасників

Обсуждение вопросов по C# / .NET / .NET Core / .NET Standard / Azure Сообщества-организаторы: — @itkpi — @dncuug