locked
Get DataTable async huge performance issue RRS feed

  • Question

  • 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!!!!!

    Wednesday, February 6, 2019 2:36 PM

All replies