Рекомендации по вставке/обновлению большого объема данных в SQL Server 2008

Я создаю систему для обновления больших объемов данных через различные CSV-каналы. Обычно я бы просто зацикливал каждую строку в фиде, делаю запрос select, чтобы проверить, существует ли элемент, и вставить/обновить элемент в зависимости от того, существует он или нет.

Я чувствую, что этот метод не очень масштабируемый и может забивать сервер на больших каналах. Мое решение состоит в том, чтобы перебирать элементы как обычные, но хранить их в памяти. Затем на каждые 100 или около того элементы выбирают из этих 100 элементов и получают список существующих элементов в базе данных, которые соответствуют. Затем объедините заявления вставки/обновления вместе и запустите их в базу данных. Это существенно сократило бы поездки в базу данных.

Является ли это достаточно масштабируемым решением и есть ли примеры учебников по импорту больших фидов в продуктивную среду?

Спасибо

Ответы

Ответ 1

Увидев, что вы используете SQL Server 2008, я бы рекомендовал этот подход:

  • сначала скопируйте ваши CSV файлы в промежуточную таблицу
  • обновите таблицу назначения из этой промежуточной таблицы с помощью команды MERGE

Просмотрите документы MSDN и отличное сообщение в блоге о том, как использовать команду MERGE.

В принципе, вы создаете связь между вашей фактической таблицей данных и промежуточной таблицей по общим критериям (например, общий первичный ключ), а затем вы можете определить, что делать, когда

  • строки соответствуют, например. строка существует как в исходной, так и в целевой таблице → как правило, вы либо обновляете некоторые поля, либо просто игнорируете все это вместе
  • строка из источника не существует в целевом объекте → обычно это случай для INSERT

У вас будет оператор MERGE примерно так:

MERGE TargetTable AS t
USING SourceTable AS src
ON t.PrimaryKey = src.PrimaryKey

WHEN NOT MATCHED THEN
  INSERT (list OF fields)
  VALUES (list OF values)

WHEN MATCHED THEN
  UPDATE
    SET (list OF SET statements)
;

Конечно, предложение ON может быть гораздо более задействовано, если это необходимо. И, конечно, ваши инструкции WHEN также могут быть более сложными, например.

WHEN MATCHED AND (some other condition) THEN ......

и т.д.

MERGE - очень мощная и очень полезная новая команда в SQL Server 2008 - используйте ее, если сможете!

Ответ 2

Ваш путь - худшее из возможных решений. В общем, вы не должны думать с точки зрения циклического прохождения записей по отдельности. Раньше у нас был встроенный в компанию инструмент импорта, который просматривал записи, чтобы загрузить файл с более чем миллионом записей, потребовалось бы 18-20 часов (что не часто случалось, когда он создавался, но во много раз день наступления сейчас).

Я вижу два варианта: во-первых, использовать массовую вставку для загрузки в промежуточную таблицу и выполнить все необходимые операции по очистке этой таблицы. Как вы определяете, существует ли запись? Вы должны быть в состоянии построить обновление на основе набора, присоединившись к промежуточной таблице в тех полях, которые определяют обновление. Часто я добавлял столбец в свою промежуточную таблицу для идентификатора записи, которой он соответствует, и заполнял ее с помощью запроса, а затем выполнял обновление. Затем вы делаете вставку записей, которые не имеют соответствующего идентификатора. Если у вас слишком много записей, чтобы делать все сразу, вы можете запускать партии (что да, это цикл), но делать партии значительно больше, чем 1 запись за раз (я обычно начинаю с 2000, а затем на основе время, которое требуется для этого определения, могу ли я сделать больше или меньше в партии).

Я думаю, что в 2008 году также есть выражение о слиянии, но у меня еще не было возможности его использовать. Ищите это в книгах онлайн.

Альтернатива - использовать SSIS, оптимизированный по скорости. SSIS - сложная вещь, а кривая обучения крутая.

Ответ 3

Один из способов - загрузить ваш CSV в DataTable (или, скорее, DataReader), а затем пакетный slam в результатах с помощью SqlBulkCopy -

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

Его довольно эффективно, и вы можете сделать сопоставление столбцов. Совет. При сопоставлении столбцов с помощью SqlBulkCopy они чувствительны к регистру.

Ответ 4

Другим подходом было бы написать хранимую процедуру .Net на сервере на сервере для работы со всем файлом...

Только если вам нужен больше контроля, чем решение Криса Крауза, хотя я - большой поклонник держать его простым (и многоразовым), где мы можем...

Ответ 5

Вам нужно вообще кататься? Можно ли предоставить данные таким образом, чтобы SQL Server мог использовать Bulk Import для его загрузки, а затем обрабатывать дубликаты в базе данных после завершения импорта?

Когда дело доходит до тяжелой работы с большим количеством данных, мой опыт, как правило, заключается в том, что работа в базе данных в максимально возможной степени намного быстрее и менее ресурсоемкой.