Проблемы с производительностью параметра table-value

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

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

Я вызываю их в .Net следующим образом:

// get the data
DataTable data = GetData();

com.CommandText = "sprocName"

// create the table-value parameter
var tvp = com.Parameters.AddWithValue("data", data);
tvp.SqlDbType = SqlDbType.Structured;

com.ExecuteNonQuery();

Я запускал профилировщик, чтобы понять, почему, и фактический оператор SQL выглядит примерно так:

declare @data table ...

insert into @data ( ... fields ... ) values ( ... values ... )
-- for each row
insert into @data ( ... fields ... ) values ( ... values ... )

sprocName(@data)

Это действительно медленный способ сделать это. Было бы намного быстрее, если бы это сделало это:

insert into @data ( ... fields ... ) 
values ( ... values ... ),
       ( ... values ... ),
       -- for each row
       ( ... values ... )

Я не уверен, почему он не использует более новый, гораздо более быстрый синтаксис. Или даже то, что он делает под капотом с SqlBulkCopy.

Новый синтаксис был добавлен в SQL 2008, но тогда также есть TVP (я думаю).

Есть ли возможность сделать это? Или что-то, что мне не хватает?

Ответ 1

Если TVP "заметно медленнее", чем другие варианты, то, скорее всего, вы не реализуете их правильно.

Вам не следует использовать DataTable, если только ваше приложение не использует его вне отправки значений в TVP. Использование интерфейса IEnumerable<SqlDataRecord> быстрее и использует меньше памяти, поскольку вы не дублируете коллекцию в памяти только для отправки ее в БД. У меня есть это в следующих местах:
- Как я могу вставить 10 миллионов записей в кратчайшие сроки? (много дополнительной информации и ссылок здесь)
- Передача словаря в хранимую процедуру T-SQL
- Потоковая передача данных в SQL Server 2008 из приложения (на SQLServerCentral.com; требуется бесплатная регистрация)
Вы не должны использовать AddWithValue для SqlParameter, хотя это вряд ли проблема с производительностью. Но все же, это должно быть:
```
SqlParameter tvp = com.Parameters.Add("data", SqlDbType.Structured);
tvp.Value = MethodThatReturnsIEnumerable<SqlDataRecord>(MyCollection);
```
TVP являются табличными переменными и поэтому не ведут статистику. Это означает, что они сообщают оптимизатору запросов только одну строку. Итак, в вашем proc, либо:
- Используйте перекомпиляцию на уровне операторов в любых запросах с использованием TVP для чего-либо, кроме простого SELECT: OPTION (RECOMPILE)
- Создайте локальную временную таблицу (т.е. одиночный #) и скопируйте содержимое TVP во временную таблицу
- Вы можете попробовать добавить кластерный первичный ключ в пользовательский тип таблицы.
- Если вы используете SQL Server 2014 или новее, вы можете попробовать использовать In-Memory OLTP/таблицы, оптимизированные для памяти. Пожалуйста, смотрите: Более быстрая временная таблица и табличная переменная с помощью оптимизации памяти

Относительно того, почему вы видите:

insert into @data ( ... fields ... ) values ( ... values ... )
-- for each row
insert into @data ( ... fields ... ) values ( ... values ... )

вместо:

insert into @data ( ... fields ... ) 
values ( ... values ... ),
       ( ... values ... ),

Если это действительно то, что происходит, то:

Если вставки выполняются внутри транзакции, то реальной разницы в производительности нет
Более новый синтаксис списка значений (то есть VALUES (row1), (row2), (row3)) ограничен чем-то вроде 1000 строк и, следовательно, не является приемлемым вариантом для TVP, у которых нет этого ограничения. ОДНАКО, это вряд ли является причиной использования отдельных вставок, учитывая, что нет никаких ограничений при выполнении INSERT INTO @data (fields) SELECT tab.[col] FROM (VALUES(),(),...) tab([col]), который я задокументировал здесь: Максимальное количество строк для конструктора табличных значений. Вместо...
Причина, скорее всего, в том, что выполнение отдельных вставок позволяет передавать значения из кода приложения в SQL Server:
1. используя итератор (т.е. IEnumerable<SqlDataRecord> отмеченный в # 1 выше), код приложения отправляет каждую строку, когда она возвращается из метода, и
2. построение списка VALUES(),(),..., даже если используется подход INSERT INTO... SELECT FROM (VALUES...) (не ограниченный 1000 строками), который все равно потребует построения целых VALUES список перед отправкой любых данных в SQL Server. Если данных много, для создания сверхдлинной строки потребуется больше времени, и при этом потребуется гораздо больше памяти.

Также ознакомьтесь с этим техническим документом от консультативной группы по SQL Server: максимальная пропускная способность с TVP

Ответ 1

Ответ 2