I'm having major SQL performance problems when using async calls. I have created a small case to demonstrate the problem.
I have create a database on a SQL Server 2016 which resides in our LAN (so not a localDB).
In that database, I have a table WorkingCopy
with 2 columns:
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]
In that table, I have inserted a single record (id
='PerfUnitTest', Value
is a 1.5mb string (a zip of a larger JSON dataset)).
Now, if I execute the query in SSMS :
SELECT [Value]
FROM [Workingcopy]
WHERE id = 'perfunittest'
I immediately get the result, and I see in SQL Servre Profiler that the execution time was around 20 milliseconds. All normal.
When executing the query from .NET (4.6) code using a plain 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;
The execution time for this is also around 20-30 milliseconds.
But when changing it to async code :
string value = await command.ExecuteScalarAsync() as string;
The execution time is suddenly 1800 ms ! Also in SQL Server Profiler, I see that the query execution duration is more than a second. Although the executed query reported by the profiler is exactly the same as the non-Async version.
But it gets worse. If I play around with the Packet Size in the connection string, I get the following results :
Packet size 32768 : [TIMING]: ExecuteScalarAsync in SqlValueStore ->
elapsed time : 450 ms
Packet Size 4096 : [TIMING]: ExecuteScalarAsync in SqlValueStore ->
elapsed time : 3667 ms
Packet size 512 : [TIMING]: ExecuteScalarAsync in SqlValueStore ->
elapsed time : 30776 ms
30,000 ms!! That's over a 1000x slower than the non-async version. And SQL Server Profiler reports that the query execution took over 10 seconds. That doesn't even explain where the other 20 seconds are gone to!
Then I've switched back to the sync version and also played around with the Packet Size, and although it did impact a little the execution time, it was nowhere as dramatic as with the async version.
As a sidenote, if it put just a small string (< 100bytes) into the value, the async query execution is just as fast as the sync version (result in 1 or 2 ms).
I'm really baffled by this, especially since I'm using the built-in SqlConnection
, not even an ORM. Also when searching around, I found nothing which could explain this behavior. Any ideas?
See Question&Answers more detail:
os