User-157143658 posted
Hi,
I need to implement a performin way to return a datatable asycronously from my webapi 2 project.
I have my old synchronous method version like this one that run on a 300.000 rows table in around 800ms:
public IHttpActionResult TestData()
{
string sql = "select * from test_async";
using (SqlConnection conn = new SqlConnection("<CONNECTION STRING>"))
{
conn.Open();
DataTable table = new DataTable();
SqlCommand cmd = new SqlCommand(sql, conn);
using (SqlDataAdapter Adapter = new SqlDataAdapter(cmd))
{
Adapter.Fill(table);
}
}
return Ok();
}
this method use Adapter.Fill() that does not exist in Async Version.
I Found a alternative way to get data using SqlDataReader class that support Async methods implementation but it take about 3000ms to complete the same task (4 times slower!!!):
public async Task<IHttpActionResult> TestDataAsync()
{
string sql = "select * from test_async";
using (SqlConnection conn = new SqlConnection("<CONNECTION STRING>"))
{
await conn.OpenAsync();
DataTable table = new DataTable();
SqlCommand cmd = new SqlCommand(sql, conn);
using (var reader = await cmd.ExecuteReaderAsync().ConfigureAwait(false))
{
DataTable schema = reader.GetSchemaTable();
if (schema != null)
{
foreach (DataRow drow in schema.Rows)
{
string columnName = System.Convert.ToString(drow["ColumnName"]);
DataColumn column = new DataColumn(columnName, (Type)(drow["DataType"]));
table.Columns.Add(column);
}
}
while (await reader.ReadAsync().ConfigureAwait(false))
{
var dataRow = table.NewRow();
for (int i = 0; i < table.Columns.Count; i++)
{
dataRow[i] = reader[i];
}
table.Rows.Add(dataRow);
}
}
}
return Ok();
}
In this test i use test_async table created using this TSQL script:
create table test_async(
id int primary key,
name text
)
DECLARE @i int = 0
while @i < 300000
begin
insert into test_async(id,name) select @i, 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco ' + cast(@i as VARCHAR)
set @i +=1
end
Anyone can tell me how can i improve the performance of the async ???
I do not want to consider hints like "use Task.Run" as a solution because i dont want to lose Async benefits.
Thanks!!!!!
