You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Reading data from a table in parallel with multiple SqlDataReaders (each reading their part of a table).
When a reader is iterated with a cancellation token created from CanellationTokenSource there is a significant performance drop.
Create the table in a MS SqlServer database.
Use the TABLE_CREATE_SQL script from the code to create a 50M record table with around 1.7GB of data.
Change the connection data in the code to your database.
Run the benchmark.
using BenchmarkDotNet.Attributes;using Microsoft.Data.SqlClient;namespace SqlReadAsync;[SimpleJob(1,1,1,1,"1",false)]publicclassBenchmarks{privateconststringSERVER="server";privateconststringDATABASE="db";privateconststringUSER="user";privateconststringPASSWORD="pass";privateconststringCONNECTION_STRING=$"Data Source={SERVER};Initial Catalog={DATABASE};User ID={USER};Password={PASSWORD};Trust Server Certificate=True;Pooling=False;";
#region TABLE SQL
privateconststringTABLE_CREATE_SQL= """
DROP TABLE IF EXISTS dbo.test_table;
WITH t1(n) AS
(
SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL
UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL
)
, t2(n) AS
(
SELECT NULL FROM t1 AS t1, t1 AS t2, t1 AS t3, t1 AS t4, t1 AS t5, t1 AS t6, t1 AS t7, t1 AS t8, t1 AS t9
)
, t3(n) AS
(
SELECT TOP 50000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM t2
)
SELECT
ID = ISNULL(t.n, 0),
c1 = CAST(t.n AS VARCHAR(12)),
d1 = DATEADD(MINUTE, t.n, '1970-01-01')
INTO dbo.test_table FROM t3 AS t;
ALTER TABLE dbo.test_table ADD CONSTRAINT PK_test_table PRIMARY KEY(ID);
""";
#endregion
privateconststringSELECT_PART="SELECT ID, c1, d1 FROM dbo.test_table";privatereadonlystring[]whereParts=[" WHERE ID > 0 AND ID <= 10000000"," WHERE ID > 10000000 AND ID <= 20000000"," WHERE ID > 20000000 AND ID <= 30000000"," WHERE ID > 30000000 AND ID <= 40000000"," WHERE ID > 40000000 AND ID <= 50000000",];publicenumReadType{Sync,Async,AsyncToken,AsyncTokenSource,}[Params(ReadType.Sync, ReadType.Async, ReadType.AsyncToken, ReadType.AsyncTokenSource)]publicReadTypeType{get;set;}privatestatic Task ReadSync(string?whereClause){usingvarconn=new SqlConnection(CONNECTION_STRING);usingvarcmd=new SqlCommand($"{SELECT_PART}{whereClause}", conn);
conn.Open();usingSqlDataReaderreader= cmd.ExecuteReader();while(reader.Read()){}return Task.CompletedTask;}privatestaticasync Task ReadAsync(stringwhereClause,CancellationTokencancellation){usingvarconn=new SqlConnection(CONNECTION_STRING);usingvarcmd=new SqlCommand($"{SELECT_PART}{whereClause}", conn){CommandTimeout=0};
conn.Open();usingSqlDataReaderreader= cmd.ExecuteReader();while(await reader.ReadAsync(cancellation).ConfigureAwait(false)){}}[Benchmark]publicasync Task TestRead(){CancellationTokencancellation= CancellationToken.None;switch(Type){case ReadType.Sync:case ReadType.Async:cancellation= CancellationToken.None;break;case ReadType.AsyncToken:cancellation=new CancellationToken();break;case ReadType.AsyncTokenSource:cancellation=new CancellationTokenSource().Token;break;}vartasks=newList<Task>();foreach(string wherePart in whereParts){switch(Type){case ReadType.Sync:
tasks.Add(Task.Run(()=> ReadSync(wherePart)));break;case ReadType.Async:
tasks.Add(Task.Run(()=> ReadAsync(wherePart, cancellation)));break;case ReadType.AsyncToken:
tasks.Add(Task.Run(()=> ReadAsync(wherePart, cancellation)));break;case ReadType.AsyncTokenSource:
tasks.Add(Task.Run(()=> ReadAsync(wherePart, cancellation)));break;}}await Task.WhenAll(tasks);}}
Expected behavior
Similar performance between runs.
Further technical details
Microsoft.Data.SqlClient version: 5.2.0
.NET target: net8.0
SQL Server version: SQL Server 2019
SQL Server docker: mcr.microsoft.com/mssql/server:2019-latest
Operating system: Windows 11, Docker container
Additional context
Problem was found on Microsoft.Data.SqlClient version 5.1.4, but still present.
The text was updated successfully, but these errors were encountered:
The performance difference is caused by registration and unregistration of a cancellation event handler in ReadAsync and cannot be avoided if you want to use a cancellation token which is provided by a CancellationTokenSource.
Describe the bug
Reading data from a table in parallel with multiple SqlDataReaders (each reading their part of a table).
When a reader is iterated with a cancellation token created from CanellationTokenSource there is a significant performance drop.
Difference in performance:
To reproduce
Create the table in a MS SqlServer database.
Use the TABLE_CREATE_SQL script from the code to create a 50M record table with around 1.7GB of data.
Change the connection data in the code to your database.
Run the benchmark.
Expected behavior
Similar performance between runs.
Further technical details
Microsoft.Data.SqlClient version: 5.2.0
.NET target: net8.0
SQL Server version: SQL Server 2019
SQL Server docker: mcr.microsoft.com/mssql/server:2019-latest
Operating system: Windows 11, Docker container
Additional context
Problem was found on Microsoft.Data.SqlClient version 5.1.4, but still present.
The text was updated successfully, but these errors were encountered: