SqlDataReader async methods (.NET 4.5 - performance troubles)

Question

Hey forum,

I have a question related to the new async methods in the .NET framework 4.5.

I'm using the following code to fetch 3.000.000 rows out of one table with 3 columns (long and 2 times varchar(150)).

private async Task<List<Customer>> GetCustomersAsync()
{
    using (var sqlConnection = new SqlConnection(ConnectionString))
    {
        await sqlConnection.OpenAsync();

        async45DataView.SetText("Data loading in progress...");

        var customers = new List<Customer>();

        using (var sqlCommand = new SqlCommand(SqlStatement, sqlConnection))
        {
            using (var sqlDataReader = await sqlCommand.ExecuteReaderAsync())
            {
                while (await sqlDataReader.ReadAsync())
                {
                    customers.Add(new Customer(sqlDataReader.GetFieldValue<long>(0),
                            sqlDataReader.GetFieldValue<string>(1),
                            sqlDataReader.GetFieldValue<string>(2)));
                }
            }
        }

        return customers;
    }
}

The data gets fetched, but I ran into some performance problems. The complete execution and the update of the DataGridView takes 30 seconds. The following 2 method will only need 5-8 seconds.

void async20DataView_FillData(object sender, EventArgs e)
{            
    var sqlConnection = new SqlConnection(ConnectionString);
    sqlConnection.Open();

    using (var sqlCommand = new SqlCommand(SqlStatement, sqlConnection))
    {
        var result = sqlCommand.BeginExecuteReader(new AsyncCallback(CommandFinished),
            sqlCommand);

        async20DataView.SetText("Data loading in progress...");
    }
}

private void CommandFinished(IAsyncResult result)
{
    var customers = new List<Customer>();

    using (var sqlCommand = result.AsyncState as SqlCommand)
    {
        using (var sqlDataReader = sqlCommand.EndExecuteReader(result))
        {
            while (sqlDataReader.Read())
            {
                customers.Add(new Customer(sqlDataReader.GetInt64(0),
                        sqlDataReader.GetString(1),
                        sqlDataReader.GetString(2)));                        
            }
        }
    }

    Invoke((MethodInvoker) delegate
    {
        async20DataView.SetData(customers);
        async20DataView.SetText("Data loading finished!");
    });
}

Can someone help me out? I don't know what I'm doing wrong.

Thank you and bye!
Traidor

---

The Visual Studio Settings-Switcher! Visit http://visualstudiogallery.msdn.microsoft.com/a79072f7-3109-44a0-95c0-9c50e729d6a3

Answer 1

Hi Twainsoft,

I'm doing research on this issue and I will let you know if I get anything.

Best Regards,

---

Bob Wu [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Hi Twainsoft,

I can't reproduce this issue so far, could you please share a demo with us?

Best Regards,

---

Bob Wu [MSFT]
MSDN Community Support | Feedback to us

Answer 3

Hi Bob Wu,

thx for your investigations and sorry for my late response.

You can download a small demo project at Twainsoft.DNP.Articles.AsyncSqlDataReader.zip

In the zip-File there's a sql script included, which can populate a database.
I can't include it in the file, because on my system (MS SQL 2012) it is more than 800 MB big.

Maybe you can find my mistakes or the general problems.

Best Regards

---

The Visual Studio Settings-Switcher! Visit http://visualstudiogallery.msdn.microsoft.com/a79072f7-3109-44a0-95c0-9c50e729d6a3