Подскажите, как работает передача табличного параметра в процедуру из DataTable?

#c_sharp #sql #net #sql_server #adonet

Подскажите, как работает передача табличного параметра в процедуру из DataTable?

Меня интересует:

1. Производительность

Будет ли это быстрее чем выполнение одиночных инструкций?

Например, в DataTable есть 1000 значений и их нужно вставить.

В одном случае я могу сделать 1000 инструкций INSERT из кода, а в другом послать
этот DataTable целиком в процедуру.

2. Ограничение

Есть ли какие-то ограничение на размер передаваемого табличного аргумента?

Например, INSERT INTO VALUES может за раз вставлять только 1000.

3. Как происходит наполнение Табличной переменной?

На сервере создается эта переменная и она заполняется одичными инструкциями INSERT
INTO из моей DataTable или что-то более интересное, например делает INSERT INTO VALUES
по 1000 записей?
    

Ответы

Ответ 1

По пункту 1: При вставке одиночными инструкциями INSERT, по-видимому, лучшее, что
можно сделать со стороны .Net для ускорения вставки - это соответствующий запрос параметризовать
и подготовить (cmd.Prepare() перед входом в цикл в нижеследующем примере):

using (var cmd = new SqlCommand())
{
    cmd.Connection = connection;
    cmd.CommandText = "insert into [Table] (ID, Name) values (@id, @name)";
    var p = new SqlParameter("@id", SqlDbType.Int);
    var p2 = new SqlParameter("@name", SqlDbType.VarChar, 20);
    cmd.Parameters.Add(p);
    cmd.Parameters.Add(p2);

    cmd.Prepare();

    for (int i = 1; i <= rowCount; i++)
    {
        p.Value = i;
        p2.Value = $"Name {i}";
        cmd.ExecuteNonQuery();
    }
}


В этом случае, благодаря параметризации, парсинг и компиляция запроса произойдут
один раз - при первом вызове ExecuteNonQuery(), а благодаря подготовке и сам текст
запроса будет передан однократно. Повторно будет передаваться уже его handle (полученный
при первом вызове), что может быть особенно выгодно, если текст запроса длинный.

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

В случае же передачи вставляемых данных через табличную переменную

DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("Name", typeof(string));

for (int i = 1; i <= rowCount; i++)
    dt.Rows.Add(i, $"Name {i}");

using (var cmd = new SqlCommand())
{
    cmd.Connection = connection;
    cmd.CommandText = @"insert into [Table] (ID, Name) select ID, Name from @data";

    var p = new SqlParameter("@data", SqlDbType.Structured);
    p.TypeName = "dbo.TableType";
    p.Value = dt;

    cmd.Parameters.Add(p);

    cmd.ExecuteNonQuery();
}


метаданные, описывающие содержимое табличного параметра, передаются не для каждой
строки, а один раз для всех строк табличной переменной. И результат возвращается тоже
один, для всех строк сразу. Что даёт выигрыш тем больший, чем больше вставляется строк.

При таком подходе, однако, можно ожидать некоторых потерь в производительности на
"перекладывании" данных (ведь данные вставляются в целевую таблицу из табличной переменной,
а не напрямую, как в случае с одиночными INSERT).

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

Моделированием это подтверждается. Для приведенных примеров,

вставка 5 строк:

M1: 132 msec
M2: 19 msec
M3: 6 msec
M4: 34 msec


вставка 10'000 строк:

M1: 13614 msec
M2: 7555 msec
M3: 5020 msec
M4: 115 msec


где M1, M2, M3 и M4 - различные методы:


M1 - одиночные вставки, не параметризованный запрос (вставка
литералов)
M2 - одиночные вставки, запрос параметризованный, но не
подготовленный
M3 - одиночные вставки, запрос параметризованный и подготовленный
M4 - вставка из табличной переменной


По пункту 2: Табличная переменная может содержать от 1 до 1024 столбцов. Число строк
произвольное и не ограничивается.

По пункту 3: Табличный параметр уходит на сервер сериализованным в TDS поток. На
стороне сервера происходит его десериализация.

Анализ профайлером на стороне SqlServer показывает, что при десериализации создаётся
соответствующая табличная переменная, которая затем заполняется примерно следующим
образом (псевдокод):

insert into @tableVariable (Column1, Column2, ...)
select Column1, Column2, ...
from TableValuedFunction()


где TableValuedFunction - по-видимому, есть некая табличная функция-обёртка поверх
входящего потока данных. Т.е. данные в табличную переменную поступают практически напрямую
из потока данных входящего запроса, минуя языковые конструкции наподобие insert into
... values ....