You get more performance by concurrency. The idea of async database operations is to release the business logics thread while the database is doing its job. This can lead a huge performance difference on heavy traffic environment (basically, will your business logics server / web-server crash or not).
In the picture, we talk about the red block, which can be released to serve other customers. As usual with async operations, there will be more thread context switching, which may cause minor performance delays, but concurrency benefits should outweigh the context switching cons.
This is the theory. In practice SQLProvider is calling implementation of async methods from abstract classes under System.Data.Common. The implementation quality of your database connection .NET drivers will define if async is good for you or not. (E.g. The current situation is that MS-SQL-server handles async well and MySQL not so.)
Currently SQLProvider supports async operations on runtime, not design-time.
Your execution thread may change. For transactions to support this, .NET 4.5.1 has a fix for asynchronous transactions that has to be explicitly used.
Concept for async queries is this:
1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29:
The functions to work with asynchrony are:
- Array.executeQueryAsync : IQueryable<'a> -> Async<<'a> >
- List.executeQueryAsync : IQueryable<'a> -> Async<'a list>
- Seq.executeQueryAsync : IQueryable<'a> -> Async
- Seq.lengthAsync : IQueryable<'a> -> Async
- Seq.headAsync : IQueryable<'a> -> Async<'a>
- Seq.tryHeadAsync : IQueryable<'a> -> Async<'a option>
- and for your data context: SubmitUpdatesAsync : unit -> Async
- Seq.sumAsync : IQueryable<'a when 'a : comparison> -> Async<'a>
- Seq.minAsync : IQueryable<'a when 'a : comparison> -> Async<'a>
- Seq.maxAsync : IQueryable<'a when 'a : comparison> -> Async<'a>
- Seq.averageAsync : IQueryable<'a when 'a : comparison> -> Async<'a>
Seq is .NET IEnumerable, which is lazy. So be careful if using Seq.executeQueryAsync to not execute your queries several times.
Usually database operations can't be executed as parallel inside one context/transaction. That is an anti-pattern in general: the network lag between database and your logics server is probably the bottleneck of your system. So, in this order:
- Try to execute your business logics as database queries, as one big query.
- Or sometimes, not often, load eagerly data with single query and process it in the logics server.
- Avoid case that you create as many queries as your collection has items.
So if you are still in the worst case, 3, and have to deal with a List
Avoid network traffic between business logics (BL) and database (DB). When you exit the query-computation, you cause the traffic.