C# - Вставка нескольких строк с помощью хранимой процедуры
У меня есть список объектов, этот список содержит приблизительно 4 миллиона объектов. существует сохраненный proc, который берет атрибуты объектов в качестве параметрических усилителей, сделайте некоторые поиски и вставьте их в таблицы.
что самый эффективный путь состоит в том, чтобы вставить это 4 миллиона объектов в дб?
Как я делаю:
-- connect to sql - SQLConnection ...
foreach(var item in listofobjects)
{
SQLCommand sc = ...
// assign params
sc.ExecuteQuery();
}
Это было действительно медленно.
существует ли лучший способ сделать это?
этот процесс будет запланированной задачей. я буду выполнять это когда-либо час, таким образом, я действительно буду ожидать данные большого объема как это.
5 ответов
Взгляните на класс SqlBulkCopy
на основе вашего комментария, выгрузите данные в промежуточную таблицу, затем выполните поиск и вставьте в реальный набор таблиц на основе процедуры .... это будет намного быстрее, чем строка за строкой
У меня были отличные результаты, используя XML для получения больших объемов данных в SQL Server. Как и вы, я изначально вставлял строки по одной, что занимало вечность из-за времени кругового пути между приложением и сервером, затем я переключил логику, чтобы передать XML-строку, содержащую все строки для вставки. Время вставки выросло с 30 минут до менее 5 секунд. Это было на пару тысяч рядов. Я тестировал XML-строки размером до 20 мегабайт, и проблем не было. В зависимости от размера строки это может быть вариантом.
Данные были переданы в виде XML-строки с использованием типа nText.
Что-то подобное сформировало основные детали хранимой процедуры, которая выполнила работу:
CREATE PROCEDURE XMLInsertPr( @XmlString ntext )
ОБЪЯВИТЬ @ReturnStatus int, @hdoc int
EXEC @ReturnStatus = sp_xml_preparedocument @hdoc ВЫХОДНЫЕ ДАННЫЕ, @XmlString
ИФ (@ReturnStatus <> 0)
НАЧИНАТЬ
РАЙЗАРРОР ('Невозможно открыть XML-документ', 16,1,50003)
ВОЗВРАТ @ReturnStatus
КОНЕЦ
ВСТАВИТЬ В Имя_таблицы
ВЫБЕРИТЕ * ИЗ OPENXML(@hdoc, '/XMLData/Data') С Именем таблицы
КОНЕЦ
Вставка четырех миллионов записей из C # никогда не будет идеальной, но лучший способ сделать это - Создайте текст команды в коде, чтобы вы могли выполнять его по частям.
Это вряд ли является пуленепробиваемым, и это не иллюстрирует, как включить поиск (как вы упомянули, что вам нужно), но основная идея такова:
// You'd modify this to chunk it out - only testing can tell you the right
// number - perhaps 100 at a time.
for(int i=0; i < items.length; i++) {
// e.g., 'insert dbo.Customer values(@firstName1, @lastName1)'
string newStatement = string.Format(
"insert dbo.Customer values(@firstName{0}, @lastName{0})", i);
command.CommandText += newStatement;
command.Parameters.Add("@firstName" + i, items[i].FirstName);
command.Parameters.Add("@lastName" + i, items[i].LastName);
}
// ...
command.ExecuteNonQuery();
- Как сказал Эйб: удалить индексы (и воссоздать позже)
- Если вы доверяете своим данным: сгенерируйте инструкцию sql для каждого вызова сохраненной процедуры, объедините некоторые и затем выполните.
Это экономит накладные расходы на общение. - Комбинированные вызовы (к сохраненной процедуре) могут быть заключены в НАЧАЛЬНУЮ ТРАНЗАКЦИЮ, поэтому у вас будет только одна фиксация на x вставок
Если это одноразовая операция: не оптимизируйте и запускайте ее ночью / в выходные
Вы можете рассмотреть возможность удаления всех индексов, которые у вас есть для таблицы (таблиц), в которую вы вставляете, а затем воссоздать их после того, как вы все вставите. Я не уверен, как работает класс массового копирования, но если вы обновляете свои индексы при каждой вставке, это может сильно замедлить работу.