Async getting data from different views of the same database

Question

I want to fetch data from 5 different views of the same database asynchronously - I used the following solution:

public async Task<List<Product>> GetProductsAsync()
    {
        string query = $"SELECT  * FROM dbo.v_Products";

        try
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();

            var items = await _dbContext.Database.SqlQuery<Product>(query).ToListAsync();

            sw.Stop();
            Debug.WriteLine("\t " + Thread.CurrentThread.ManagedThreadId + $" getting Products ({items.Count}) seconds: " + sw.Elapsed.TotalSeconds);

            return items;
        }
        catch (Exception ex)
        {
            throw new Exception("Getting Products failed!", ex);
        }
    }

There is the following situation: I have ~30 databases, a thread is ran for each one and methods like "GetProductAsync" are executed for gathering data. But I haven' see improvement of using async, it seems that time of execution of each next method contains the time of execution of previous. Where could I be wrong?

UPD: Calling the function

public async Task<DataContext> GetDataAsync()
    {
        DataContext data = new DataContext();

        var items1= await _dac.GetProductsAsync();
        var items2 = await _dac.GetProducts2Async();
        var items3 = await _dac.GetProducts3Async();
        var items4 = await _dac.GetProducts4Async();
        var items5 = await _dac.GetProducts5Async();

        data.items1= items1;
        data.items2= items2;
        data.items3= items3;
        data.items4= items4;
        data.items5= items5;

        return data;
    }

Is it OK if I will recreate db context for each async method execution, like here?

public async Task<List<Product>> GetProductsAsync()
{
    string query = $"SELECT  * FROM dbo.v_Products";

var ctx = new myDbContext(_dbContext.Database.ConnectionString);

    try
    {
        Stopwatch sw = new Stopwatch();
        sw.Start();

        var items = await ctx.Database.SqlQuery<Product>(query).ToListAsync();

        sw.Stop();
        Debug.WriteLine("\t " + Thread.CurrentThread.ManagedThreadId + $" getting Products ({items.Count}) seconds: " + sw.Elapsed.TotalSeconds);

        return items;
    }
    catch (Exception ex)
    {
        throw new Exception("Getting Products failed!", ex);
    }
}

await blocks execution, so your 5 queries run sequentially one after other — 3615
– 3615, Commented Mar 21, 2017 at 11:48

Sarvesh Mishra · Accepted Answer · 2017-03-21 12:19:55Z

2

Call all Async methods but Await all of them later,

public async Task<DataContext> GetDataAsync()
{
    DataContext data = new DataContext();

    var t1 = _dac.GetProductsAsync();
    var t2 = _dac.GetProducts2Async();
    var t3 = _dac.GetProducts3Async();
    var t4 = _dac.GetProducts4Async();
    var t5 = _dac.GetProducts5Async();

    data.items1 = await t1;
    data.items2 = await t2;
    data.items3 = await t3;
    data.items4 = await t4;
    data.items5 = await t5;

    return data;
}

Creating new context for each call is not going to create any problem as long as the execution time of database call is small. Each new connection uses/reuses connection from a connection pool so you should not eat up all of it.

edited Mar 21, 2017 at 12:19

answered Mar 21, 2017 at 11:48

Sarvesh Mishra

2,08215 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dzmitry Shauchuk Over a year ago

Please, take a look at the my question above about it - is it OK?

Serg · Accepted Answer · 2017-03-21 11:49:41Z

1

Most probably because

Multiple active operations on the same context instance are not supported.

Your calling method does exactly what it should do:

Use 'await' to ensure that any asynchronous operations have completed before calling another method on this context.

https://msdn.microsoft.com/en-us/library/dn220262(v=vs.113).aspx

answered Mar 21, 2017 at 11:49

Serg

22.8k5 gold badges23 silver badges50 bronze badges

Comments

udaya kumar · Accepted Answer · 2017-03-21 12:09:30Z

1

 public async Task<DataContext> GetDataAsync()
{
    DataContext data = new DataContext();
//crate individual tasks
    var test1 = _dac.GetProductsAsync();
    var test2 = _dac.GetProducts2Async();
    var test3 = _dac.GetProducts3Async();
    var test4 = _dac.GetProducts4Async();
    var test5 = _dac.GetProducts5Async();
//Execute all tasks at once with WhenAll function
    await Task.WhenAll(task1, task2, task3, task4, task5); 
//This statement is executed only after all the tasks are finished  
    return data;
}

Refer MSDN Link for detailed notes on WhenAll.

answered Mar 21, 2017 at 12:09

udaya kumar

2911 silver badge9 bronze badges

Collectives™ on Stack Overflow

Async getting data from different views of the same database

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related