Parallel calls to a SQL Server database from view model

Question

I have simple view model, which is populating itself but the problem is that a webpage contains a lot of select lists and for every one select list I am calling database procedure for getting data list.

Is it possible, for performance, to execute database calls asynchronous or in parallel?

I have this kind of code :

// controller
public ActionResult Index()
{
    model = new SampleViewModel();    
    model.Populate(database);
    return View(model);
}

// view model
public class SampleViewModel
{
    public SampleViewModel(DbContext db)
    {
        _list1 = context.Db.SqlQuery<SelectList1>("SELECT Id, Value FROM dbo.Table1").ToList();
        _list2 = context.Db.SqlQuery<SelectList2>("SELECT Id, Value FROM dbo.Table2").ToList(); 
        _list3 = context.Db.SqlQuery<SelectList3>("SELECT Id, Value FROM dbo.Table3").ToList(); 
        _list4 = context.Db.SqlQuery<SelectList4>("SELECT Id, Value FROM dbo.Table4").ToList(); 
        _list5 = context.Db.SqlQuery<SelectList5>("SELECT Id, Value FROM dbo.Table5").ToList();  
    }

    private readonly List<SelectList1> _list1;
        public int SelectedList1Id { get; set; }
        public IEnumerable<SelectListItem> List1 { get { return new SelectList(_list1, "Id", "Value");} }

    -//- _list2
    -//- _list3
    -//- _list4
    -//- _list5
}

As you can see, _list3 is waiting for _list2 and _list2 is waiting for _list1 and this can slow request a lot. The reason why view model is populating itself is because in real scenario these select lists are related to each other and the model contains information about selected Ids and with these Ids I can rebuild the select lists for example if model validation failed.

Any idea? Can I use some async await approach and will it help me in this case against SQL Server 2008 ?

(psst, it's Model View Controller--that's not a "view model", it's a Model) — user1228
– user1228, Commented Aug 9, 2016 at 18:17

Shyju · Accepted Answer · 2016-08-09 18:56:04Z

1

You may use Task Parallel Libraries Paralle.Invoke method to execute many tasks in parallel.

Parallel.Invoke(() =>{
    // Execute some code here    
}, () =>
{
     // Execute some other code here  
});

I personally do not pass a concrete DbContext object to my view model. View models should be simple POCO. It should not have any knowledge of your data access technology. So my personal preference is keeping the data access code seperate from my view model. So i never read values from database in a view model constructor with a concrete object like you did.

Assuming you have a simply POCO view model like this

public class CreateViewModel
{
  public List<SelectListItem> States {set;get;}
  public List<SelectListItem> UserTypes {set;get;}
}

In your GET action, you can use Parallel.Invoke to load the 2 properties data.

var vm = new CreateViewModel();
Parallel.Invoke(() =>{
    vm.States = db.States.Select(s=>new SelectListItem { Value=s.Id.ToString(), 
                                                         Text=s.Name }).ToList();
}, () =>
{
    vm.UserTypes= db.UserTypes.Select(s=>new SelectListItem { Value=s.Id.ToString(),
                                                              Text=s.Name }).ToList();
});
return View(vm);

Caching

If these are frequently accessed items for your dropdown, I suggest you cache this data instead of querying the db table every time. You may consider using the default MemoryCache.

edited Aug 9, 2016 at 18:56

answered Aug 9, 2016 at 18:50

Shyju

220k106 gold badges421 silver badges499 bronze badges

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

2 Comments

Muflix Over a year ago

thank you i will try it ! :-) the reason i populate view model itself is because i dont want to have fat controller or add another service layer, i am not so experienced yet, so maybe in next project i will try some different aproach

Muflix Over a year ago

also i dont want to use a cache because this problem is at backend for administrators but the idea it invoke is to track last update time of database table and if it change, reset cache.. i will also think about it

Collectives™ on Stack Overflow

Parallel calls to a SQL Server database from view model

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related