Проблемы с производительностью параметра 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 "заметно медленнее", чем другие варианты, то, скорее всего, вы не реализуете их правильно.

  1. Вам не следует использовать DataTable, если только ваше приложение не использует его вне отправки значений в TVP. Использование интерфейса IEnumerable<SqlDataRecord> быстрее и использует меньше памяти, поскольку вы не дублируете коллекцию в памяти только для отправки ее в БД. У меня есть это в следующих местах:

  2. Вы не должны использовать AddWithValue для SqlParameter, хотя это вряд ли проблема с производительностью. Но все же, это должно быть:

    SqlParameter tvp = com.Parameters.Add("data", SqlDbType.Structured);
tvp.Value = MethodThatReturnsIEnumerable<SqlDataRecord>(MyCollection);
  3. 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

Ответ 2

См. раздел "Параметры табличных значений или операций BULK INSERT"

http://msdn.microsoft.com/en-us/library/bb510489.aspx

Цитата: "... табличные параметры хорошо работают для вставки менее 1000 строк".

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

Надеюсь, это поможет, удачи.