none
SqlDataReader async methods (.NET 4.5 - performance troubles) RRS feed

  • 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

    • Moved by Iric Wen Wednesday, September 12, 2012 5:44 AM (From:SQL Server Data Access)
    Monday, September 10, 2012 2:56 PM

Answers

  • 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


    • Edited by Twainsoft Thursday, October 25, 2012 2:33 PM Fixed the link.
    • Marked as answer by Twainsoft Sunday, August 24, 2014 7:50 PM
    Thursday, October 25, 2012 2:32 PM

All replies

  • 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

    Thursday, September 13, 2012 10:57 AM
    Moderator
  • 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

    Monday, September 17, 2012 11:26 AM
    Moderator
  • 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


    • Edited by Twainsoft Thursday, October 25, 2012 2:33 PM Fixed the link.
    • Marked as answer by Twainsoft Sunday, August 24, 2014 7:50 PM
    Thursday, October 25, 2012 2:32 PM