Самый быстрый способ обновить (заполнить) 1 000 000 записей в базе данных с помощью.NET
Я использую этот код для вставки 1 миллиона записей в пустую таблицу в базе данных. Итак, без особого кода я начну с того момента, когда уже взаимодействовал с данными, и прочту схему в DataTable:
Так:
DataTable returnedDtViaLocalDbV11 = DtSqlLocalDb.GetDtViaConName(strConnName, queryStr, strReturnedDtName);
И теперь, когда у нас есть returnedDtViaLocalDbV11 давайте создадим новый 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) за 5200 мс. Остальная часть кода просто реализует массовую копию, но я все равно опубликую ее.
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;
}
}
Попытка его через соединение с сервером SQL заняла около 7000 мс (в лучшем случае) и ~7700 мс в среднем. Кроме того, через случайную базу данных kv nosql заняло около 40 секунд (на самом деле я даже не вел ее записи, поскольку она проходила через x2 вариантов sql). Итак... есть ли более быстрый способ, чем то, что я тестировал в своем коде?
редактировать
Я использую Win7 x64 8 ГБ оперативной памяти, и самое главное, я думаю, что (как i5 3 ГГц) не так уж велика, что x3 500 ГБ WD на Raid-0 делает работу еще лучше, но я просто говорю, если вы проверите на своем компьютере, хотя просто сравните его с любым другим методом в вашей конфигурации
8 ответов
Вы пробовали SSIS? Я никогда не писал пакет служб SSIS с подключением loacldb, но этот вид деятельности должен хорошо подходить для служб SSIS.
Если ваш источник данных - SQL Server, другой идеей будет настройка связанного сервера. Не уверен, что это будет работать с localdb. Если вы можете настроить связанный сервер, вы можете обойти C# все вместе и загрузить свои данные с помощью оператора SQL INSERT .. SELECT ... FROM ....
Ты можешь использовать Dapper.NET, Dapper является микро-ORM, выполняет запрос и отображает результаты в строго типизированный список. Объектно-реляционное отображение (ORM, O/RM и O/R mapping) в компьютерном программном обеспечении - это метод программирования для преобразования данных между несовместимыми системами типов в объектно-ориентированных языках программирования. В результате создается "база данных виртуальных объектов", которую можно использовать из языка программирования.
Для получения дополнительной информации:
проверьте https://code.google.com/p/dapper-dot-net/
Репозиторий GitHub: https://github.com/SamSaffron/dapper-dot-net Надеюсь, что это помогает..
Удалить зацикливание... В SQL попытайтесь создать таблицу с 1 миллионом строк... и оставьте соединение с ней, используйте это для вставки / выбора данных
Если вы просто создаете бессмысленные данные, создайте хранимую процедуру и просто вызовите ее через.net
Если вы передаете реальные данные, повторная передача их в хранимый процесс будет быстрее, но вам лучше отказаться от таблицы и воссоздать ее с данными.
Если вы вставляете по одной строке за раз, это займет больше времени, чем вставка всего сразу. Это займет еще больше времени, если у вас есть индексы для записи.
Если это новый проект, я рекомендую вам использовать Entity Framework. В этом случае вы можете создать List<> с объектом со всеми необходимыми данными, а затем просто полностью добавить его в соответствующую таблицу. Таким образом, вы быстро получаете необходимые данные и сразу же отправляете их в базу данных.
Я согласен с Майком по SSIS, но он может не подходить для вашей среды, однако для процессов ETL, которые включают кросс-серверные вызовы и общие процессы передачи данных, это отличный встроенный инструмент и высокоинтегрированный.
С 1 миллионами строк вам, скорее всего, придется выполнить массовую вставку. В зависимости от размера строки, вы не сможете использовать хранимую процедуру, если вы не сделали это в пакетном режиме. Dataatable заполнит память довольно быстро, опять же, в зависимости от размера строки. Вы могли бы создать хранимую процедуру, которая бы принимала табличный тип и вызывать это каждые X строк, но зачем нам это делать, когда у вас уже есть лучшее, более масштабируемое решение. Этот миллион строк может составить 50 миллионов в следующем году.
Я немного использовал SSIS, и если бы это подходило для организации, я бы посоветовал взглянуть на него, но это был бы не однократный ответ, не стоило бы зависимости.
Создайте единый файл XML для всех строк, которые вы хотите сохранить в базе данных. Передайте этот XML в хранимую процедуру SQL и сохраните все записи только в одном вызове. Но ваша хранимая процедура должна быть написана так, чтобы она могла прочитать все прочитанные, а затем вставить в таблицу.
Попробуйте отправить его без сохранения данных.
Посмотрите пример в конце этого поста, который позволяет вам сделать это с помощью перечислителя http://www.developerfusion.com/article/122498/using-sqlbulkcopy-for-high-performance-inserts/