Ужасная производительность при использовании асинхронных методов SqlCommand с большими данными

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

Я создал базу данных на SQL Server 2016, которая находится в нашей локальной сети (а не в localDB).

В этой базе данных у меня есть таблица WorkingCopy с двумя столбцами:

Id (nvarchar(255, PK))
Value (nvarchar(max))

DDL

CREATE TABLE [dbo].[Workingcopy]
(
    [Id] [nvarchar](255) NOT NULL, 
    [Value] [nvarchar](max) NULL, 

    CONSTRAINT [PK_Workingcopy] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

В этой таблице я вставил одну запись (id= 'PerfUnitTest', Value - это строка 1.5mb (zip большего набора данных JSON)).

Теперь, если я выполняю запрос в SSMS:

SELECT [Value] 
FROM [Workingcopy] 
WHERE id = 'perfunittest'

Я сразу получаю результат, и я вижу в SQL Servre Profiler, что время выполнения составляет около 20 миллисекунд. Все нормально.

При выполнении запроса из кода .NET(4.6) с помощью простого SqlConnection:

// at this point, the connection is already open
var command = new SqlCommand($"SELECT Value FROM WorkingCopy WHERE Id = @Id", _connection);
command.Parameters.Add("@Id", SqlDbType.NVarChar, 255).Value = key;

string value = command.ExecuteScalar() as string;

Время выполнения для этого также составляет около 20-30 миллисекунд.

Но при смене его на асинхронный код:

string value = await command.ExecuteScalarAsync() as string;

Время выполнения внезапно 1800 мс! Также в SQL Server Profiler я вижу, что продолжительность выполнения запроса больше секунды. Хотя выполненный запрос, сообщенный профилировщиком, точно такой же, как версия, отличная от Async.

Но все ухудшается. Если я играю с размером пакета в строке подключения, я получаю следующие результаты:

Размер пакета 32768: [ВРЕМЯ]: ExecuteScalarAsync в SqlValueStore → прошедшее время: 450 мс

Размер пакета 4096: [ВРЕМЯ]: ExecuteScalarAsync в SqlValueStore → прошедшее время: 3667 мс

Размер пакета 512: [TIMING]: ExecuteScalarAsync в SqlValueStore → прошедшее время: 30776 мс

30 000 мс! Это на 1000 раз медленнее, чем не-асинхронная версия. И SQL Server Profiler сообщает, что выполнение запроса заняло более 10 секунд. Это даже не объясняет, куда уходят другие 20 секунд!

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

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

Я действительно смущен этим, тем более, что я использую встроенный SqlConnection, даже не ORM. Кроме того, при поиске вокруг я не нашел ничего, что могло бы объяснить это поведение. Любые идеи?

Ответ 1

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

Сколько накладных расходов? Давайте посмотрим на ваши временные числа. 30 мс для блокирующего вызова, 450 мс для асинхронного вызова. Размер пакета 32 КБ означает, что вам нужно около пятидесяти отдельных операций ввода-вывода. Это означает, что у нас примерно 8 мс служебных данных на каждый пакет, что очень хорошо соответствует вашим измерениям для пакетов разных размеров. Это не похоже на издержки только из-за асинхронности, хотя асинхронные версии должны выполнять гораздо больше работы, чем синхронные. Похоже, синхронная версия представляет собой (упрощенно) 1 запрос → 50 ответов, в то время как асинхронная версия заканчивается 1 запросом → 1 ответом → 1 запрос → 1 ответом ->..., оплачивая стоимость снова и снова снова.

Идем глубже. ExecuteReader работает так же хорошо, как и ExecuteReaderAsync. Следующей операцией является Read а затем GetFieldValue - и там происходит интересная вещь. Если какой-либо из них является асинхронным, вся операция выполняется медленно. Так что, безусловно, что-то совсем другое происходит, когда вы начинаете делать вещи действительно асинхронными - Read будет быстрым, а затем асинхронный GetFieldValueAsync будет медленным, или вы можете начать с медленного ReadAsync, а затем GetFieldValue и GetFieldValueAsync будут быстрыми. Первое асинхронное чтение из потока является медленным, и медлительность полностью зависит от размера всей строки. Если я добавлю больше строк одинакового размера, чтение каждой строки займет столько же времени, как если бы у меня была только одна строка, так что очевидно, что данные все еще передаются поток за строкой - просто кажется, что они предпочитают читать все Строка сразу, как только вы начнете любое асинхронное чтение. Если я читаю первую строку асинхронно, а вторую синхронно - вторая читаемая строка снова будет быстрой.

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

Лучшее выступление, которое я получил, было при правильном выполнении всего этого. Это означает использование CommandBehavior.SequentialAccess, а также явную потоковую передачу данных:

using (var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SequentialAccess))
{
  while (await reader.ReadAsync())
  {
    var data = await reader.GetTextReader(0).ReadToEndAsync();
  }
}

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

Если вам нужна высокая производительность в пограничных случаях, убедитесь, что вы используете лучшие инструменты, доступные в этом случае, - в этом случае потоковая ExecuteScalar больших данных столбца, а не использование таких помощников, как ExecuteScalar или GetFieldValue.