SqlBulkCopy Обработка ошибок/продолжение при ошибке

Я пытаюсь вставить огромное количество данных в SQL-сервер. Моя целевая таблица имеет уникальный индекс под названием «Хэш».

Я хотел бы заменить свою реализацию SqlDataAdapter на SqlBulkCopy. В SqlDataAapter есть свойство, называемое «ContinueUpdateOnError», если для него установлено значение «истина». Обновление (таблица) вставит все возможные строки и пометит строки ошибок свойством RowError.

Вопрос в том, как я могу использовать SqlBulkCopy для максимально быстрой вставки данных, при этом отслеживая, какие строки были вставлены, а какие нет (из-за уникального индекса)?

Вот дополнительная информация:

  1. Процесс является итеративным, часто его повторяют по расписанию.

  2. Исходная и целевая таблицы могут быть огромными, иногда миллионы строк.

  3. Несмотря на то, что можно сначала проверить хэш-значения, для каждой строки требуется две транзакции (сначала для выбора хэша из целевой таблицы, а затем выполнения вставки). Я думаю, что в случае с adapter.update(table) быстрее проверить RowError, чем проверять совпадения хэшей для каждой строки.


c# ado.net sqlbulkcopy
person Paladin    schedule 17.06.2009    source источник
comment
Почему бы не удалить уже существующие строки из набора вставляемых данных перед попыткой вставки?   -  person ChrisW    schedule 17.06.2009
comment
ChrisW, ценю ваше предложение. Если вы предлагаете мне выполнить этот запрос, удалите его из таблицы, где находится хеш (значение1, значение2, ..). Я не могу использовать этот подход, потому что он будет обрабатывать каждую строку так, как если бы она не существовала в целевой таблице, однако другие части моей системы любят обрабатывать только новые записи (те, у которых нет коллизии хэшей).   -  person Paladin    schedule 17.06.2009
comment
@Paladin, см. расширенный ответ   -  person Sam Saffron    schedule 17.06.2009

Ответы (3)


arrow_upward
7
arrow_downward

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

Тем не менее, это быстро, действительно очень быстро.

Если вы хотите обойти проблему дублирования ключа и определить, какие строки являются дубликатами в пакете. Один из вариантов:

  • начать транс
  • Захватите tablockx на столе, выберите все текущие значения «Hash» и бросьте их в HashSet.
  • Отфильтруйте дубликаты и сообщите.
  • Вставьте данные
  • совершить транзакцию

Этот процесс будет работать эффективно, если вы вставляете огромные множества и размер исходных данных в таблице не слишком велик.

Не могли бы вы расширить свой вопрос, чтобы включить остальную часть контекста проблемы.

ИЗМЕНИТЬ

Теперь, когда у меня есть больше контекста, вот еще один способ сделать это:

  • Выполните массовую вставку во временную таблицу.
  • запустить сериализуемый транс
  • Выберите все временные строки, которые уже есть в целевой таблице... сообщите о них
  • Вставьте данные из временной таблицы в реальную таблицу, выполнив левое соединение с хэшем и включив все новые строки.
  • совершить транс

Этот процесс очень прост при круговых поездках, и, учитывая ваши спецификации, он должен быть очень быстрым;

person Sam Saffron    schedule 17.06.2009
comment
Мне действительно нужно это решение, но я не являюсь продвинутым пользователем SQL и не понимаю, как работает сериализуемый транс. не могли бы вы немного помочь понять? - person pato.llaguno; 01.09.2016

arrow_upward
4
arrow_downward

Несколько иной подход, чем уже предложенный; Выполните SqlBulkCopy и перехватите сгенерированное SqlException:

    Violation of PRIMARY KEY constraint 'PK_MyPK'. Cannot insert duplicate 
key in object 'dbo.MyTable'. **The duplicate key value is (17)**.

Затем вы можете удалить все элементы из своего источника с идентификатором 17, первой дублированной записью. Здесь я делаю предположения, которые применимы к моим обстоятельствам и, возможно, не к вашим; т. е. дублирование вызвано теми же точными данными из ранее неудачного SqlBulkCopy из-за ошибок SQL/сети во время загрузки.

person Oli B    schedule 10.06.2014

arrow_upward
1
arrow_downward

Примечание. Это краткое изложение ответа Сэма с более подробной информацией.

Спасибо Сэму за ответ. Я поместил это в ответ из-за нехватки места в комментарии.

Исходя из вашего ответа, я вижу два возможных подхода:

Решение 1:

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

решение 2:

  • Создайте временную таблицу, чтобы отразить схему таблицы назначения
  • массовая вставка во временную таблицу
  • начать сериализуемую транзакцию
  • Получить повторяющиеся строки: «выбрать хэш из tempTable, где tempTable.hash=destinationTable.hash»
  • отчет о повторяющихся строках
  • Вставьте данные из временной таблицы в целевую таблицу: «выберите * в целевую таблицу из temptable левого соединения temptable.hash = destinationTable.hash, где дестинацияTable.hash имеет значение null»
  • совершить транс

Поскольку у нас есть два подхода, все сводится к тому, какой из них наиболее оптимизирован? Оба подхода должны извлекать повторяющиеся строки и сообщать, в то время как второй подход требует дополнительных действий:

  • создание и удаление временной таблицы
  • еще одна команда sql для перемещения данных из временной таблицы в целевую
  • зависит от процента коллизии хешей, так же по проводу передается много ненужных данных

Если это единственные решения, мне кажется, что первый подход побеждает. Что вы думаете, ребята? Спасибо!

person Paladin    schedule 18.06.2009
comment
Паладин, вы неправильно понимаете второй подход. Он не требует передачи полного списка хэшей клиенту, а вместо этого выполняет анализ дубликатов на сервере. Это делает его масштабируемым намного лучше, поскольку в уже существующей таблице уже есть тонна данных. - person Sam Saffron; 18.06.2009
comment
Сэм, вы говорите о шаге Получить повторяющиеся строки: выбрать хэш из tempTable, где tempTable.hash=destinationTable.hash? Этот шаг не передает клиенту полный список хэшей, а только хэши соответствия/коллизии, которые мне в любом случае нужны на клиенте. Он точно выбирает все временные строки, которые уже находятся в целевой таблице... сообщает о них. Не могли бы вы подробнее рассказать о процессе анализа дубликатов? Спасибо! - person Paladin; 18.06.2009