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

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

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

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

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

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

причины, по которым я хотел бы заменить BULK INSERT с ТВП являются:

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

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

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

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

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

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

---

Update: теперь с 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 в конечном итоге работал лучше в среднем, даже когда данные превысил 1000 строк на небольшое количество.

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

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

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

---

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

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

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

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