Я собираюсь, должны переписать некоторый довольно старый код с помощью SQL Server BULK INSERT управляйте, потому что схема изменилась, и мне пришло в голову, что, возможно, я должен думать о переключении на хранимую процедуру с TVP вместо этого, но я задаюсь вопросом, какой эффект это могло бы иметь на производительность.

Некоторая справочная информация, которая могла бы помочь объяснить, почему я задаю этот вопрос:

• Данные на самом деле прибывают на пути веб-сервис. Веб-сервис пишет текстовый файл в совместно используемую папку на сервере базы данных, который в свою очередь выполняет a BULK INSERT. Этот процесс был первоначально реализован на SQL Server 2000, и в то время, когда не было действительно никакой альтернативы кроме зажимания нескольких сотен INSERT операторы в сервере, который на самом деле был исходным процессом и был аварией производительности.

• Данные объемные вставленный в постоянную таблицу подготовки и затем объединенный в намного большую таблицу (после которого они удалены из таблицы подготовки).

• Объем данных для вставки является "большим", но не "огромным" - обычно несколько сотен строк, возможно, 5-10k вершины строк в редких экземплярах. Поэтому мое инстинктивное чувство - это BULK INSERT быть незарегистрированной операцией не сделает это большим различие (но конечно я не уверен, следовательно вопрос).

• Вставка является на самом деле частью намного большей конвейерной пакетной обработки и должна произойти много раз по очереди; поэтому производительность очень важна.

Причины я хотел бы заменить BULK INSERT с TVP:

• Запись текстового файла по NetBIOS, вероятно, уже стоит некоторого времени, и это довольно ужасно с архитектурной точки зрения.

• Я полагаю, что таблица подготовки может (и если) быть устраненной. Главная причина, которая это, состоит в том, что вставленные данные должны использоваться для нескольких других обновлений одновременно вставки, и это является намного более дорогостоящим для попытки обновления от крупной производственной таблицы, чем это должно использовать почти пустую таблицу подготовки. С TVP параметр в основном является таблицей подготовки, я могу сделать что-либо, что я хочу с ним перед/после того, как основной вставкой.

• Я мог в значительной степени покончить с проверкой простофили, кодом очистки, и все издержки, связанные с объемом, вставляют.

• Никакая потребность волноваться о конкуренции за блокировку на таблице подготовки или tempdb, если сервер получает несколько из этих транзакций сразу (мы стараемся избегать его, но это происходит).

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

Таким образом - для кого-либо, кто является достаточно удобным с SQL Server 2008, чтобы попробовать или по крайней мере исследовать это, каков вердикт? Для вставок, скажем, несколько сотен к нескольким тысячам строк, происходящих на довольно частой основе, TVPs сокращают горчицу? Существует ли значительная разница в производительности, сравненной с объемом, вставляет?

Обновление: Теперь с на 92% меньшим количеством вопросительных знаков!

(Иначе: результаты испытаний)

Конечный результат теперь работает после того, что чувствует себя подобно 36-этапному процессу развертывания. Оба решения были экстенсивно протестированы:

• Срывание кода совместно используемой папки и использование SqlBulkCopy классифицируйте непосредственно;
• Переключение на хранимую процедуру с TVPs.

Просто, таким образом, читатели могут понять то, что точно было протестировано, для смягчения любых сомнений относительно надежности этих данных, вот более подробное объяснение того, что на самом деле делает этот процесс импорта:

1. Запустите с временной последовательности данных, которая обычно является приблизительно 20-50 точками данных (хотя это могут иногда быть несколько сотен);

2. Сделайте целый набор сумасшедшей обработки на нем, это главным образом независимо от базы данных. Этот процесс параллелизируется, таким образом, приблизительно 8-10 из последовательностей в (1) обрабатываются одновременно. Каждый параллельный процесс генерирует 3 дополнительных последовательности.

3. Возьмите все 3 последовательности и исходную последовательность и объедините их в пакет.

4. Объедините пакеты от всех 8-10 теперь законченных задач обработки в один большой суперпакет.

5. Импортируйте его с помощью любого BULK INSERT стратегия (см. следующий шаг), или стратегия TVP (пропуск к шагу 8).

6. Используйте SqlBulkCopy класс для дампа всего суперпакета в 4 постоянных таблицы подготовки.

7. Выполните Хранимую процедуру, что (a) выполняет набор шагов агрегирования на 2 из таблиц, включая несколько JOIN условия, и затем (b) выполняют a MERGE на 6 производственных таблицах с помощью и агрегированных и неагрегированных данных. (Законченный)

   ИЛИ

8. Генерируйте 4 DataTable объекты, содержащие данные, которые будут объединены; 3 из них содержат типы CLR, которые, к сожалению, правильно не поддерживаются ADO.NET TVPs, таким образом, они должны проталкиваться как строковые представления, который повреждает производительность немного.

9. Подайте TVPs к Хранимой процедуре, которая делает по существу ту же обработку как (7), но непосредственно с полученными таблицами. (Законченный)

Результаты были довольно близки, но подход TVP в конечном счете работал лучше в среднем, даже когда данные превысили 1 000 строк небольшим количеством.

Обратите внимание, что этот процесс импорта выполняется много тысяч времен по очереди, таким образом, было очень легко получить среднее время просто путем подсчета, сколько часов (да, часов) потребовалось для окончания всех слияний.

Первоначально, среднее слияние заняло почти точно 8 секунд для завершения (под нормальной нагрузкой). Удаление клуджа NetBIOS и переключение на SqlBulkCopy уменьшенный время до почти точно 7 секунд. Переключение на TVPs далее уменьшило время до 5,2 секунд на пакет. Это - 35%-е улучшение пропускной способности для процесса, время выполнения которого измеряется в часах - так не плохо вообще. Это - также законченное улучшение на ~25% SqlBulkCopy.

Я на самом деле довольно уверен, что истинное улучшение было значительно больше, чем это. Во время тестирования стало очевидно, что заключительное слияние больше не было критическим путем; вместо этого, веб-сервис, который делал всю обработку данных, начинал прогибаться под количеством входящих запросов. Ни ЦП, ни база данных I/O не были действительно истрачены, и не было никакого значительного действия блокировки. В некоторых случаях мы видели разрыв нескольких неактивных секунд между последовательными слияниями. Был небольшой разрыв, но намного меньший (половина приблизительно секунды) при использовании SqlBulkCopy. Но я предполагаю, что это станет рассказом в течение другого дня.

Заключение: табличные Параметры действительно работают лучше, чем BULK INSERT операции для комплекса import+transform процессы, воздействующие на наборы данных среднего размера.

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

Другими словами, мы уже имеем более чем достаточно понимания процесса и не нуждаемся в безопасности таблицы подготовки; единственная причина у нас была таблица подготовки во-первых, состояла в том, чтобы не перегружаться на всем из INSERT и UPDATE операторы, которые мы должны были бы использовать иначе. В исходном процессе данные подготовки только жили в таблице подготовки для частей секунды так или иначе, таким образом, это не добавило значения в условиях обслуживания/пригодности для обслуживания.

Также обратите внимание, что мы не заменили каждый BULK INSERT операция с TVPs. Несколько операций, которые имеют дело с большими объемами данных и/или не должны делать ничего специального с данными кроме броска это в DB все еще, используют SqlBulkCopy. Я не предполагаю, что TVPs являются панацеей производительности, только что они успешно выполнились SqlBulkCopy в этом определенном экземпляре, включающем несколько преобразований между начальной подготовкой и заключительным слиянием.

Таким образом, там у Вас есть он. Точка переходит к TToni для нахождения наиболее необходимой ссылки, но я ценю другие ответы также. Еще раз спасибо!