CODEWITHSUNDEEP
c#asp.netentity-frameworkt-sqlasynchronous
Dave
Dave

Reputation: 263

Asynchronous DB-Query to trigger Stored Procedure

I want to performa an asynchronous DB Query in C# that calls a stored procedure for a Backup. Since we use Azure this takes about 2 minutes and we don't want the user to wait that long.

So the idea is to make it asynchronous, so that the task continues to run, after the request.

[HttpPost]
public ActionResult Create(Snapshot snapshot)
{
    db.Database.CommandTimeout = 7200;
    Task.Run(() => db.Database.ExecuteSqlCommandAsync("EXEC PerformSnapshot @User = '" + CurrentUser.AccountName + "', @Comment = '" + snapshot.Comment + "';"));
    this.ShowUserMessage("Your snapshot has been created.");
    return this.RedirectToActionImpl("Index", "Snapshots", new System.Web.Routing.RouteValueDictionary());
}

I'm afraid that I haven't understood the concept of asynchronous taks. The query will not be executed (or aborted?), if I don't use the wait statement. But actually "waiting" is the one thing I espacially don't want to do here.

So... why am I forced to use wait here?

Or will the method be started, but killed if the requst is finished?

Upvotes: 2

Views: 2041

Answers (4)

Todd Menier
Todd Menier

Reputation: 39329

We don't want the user to wait that long.

async-await won't help you with that. Odd as it may sound, the basic async-await pattern is about implementing synchronous behavior in a non-blocking fashion. It doesn't re-arrange your logical flow; in fact, it goes to great lengths to preserve it. The only thing you've changed by going async here is that you're no longer tying up a thread during that 2-minute database operation, which is a huge win your app's scalability if you have lots of concurrent users, but doesn't speed up an individual request one bit.

I think what you really want is to run the operation as a background job so you can respond to the user immediately. But be careful - there are bad ways to do that in ASP.NET (i.e. Task.Run) and there are good ways.

Upvotes: 1

Stephen Cleary
Stephen Cleary

Reputation: 456917

As I describe in my MSDN article on async ASP.NET, async is not a silver bullet; it doesn't change the HTTP protocol:

When some developers learn about async and await, they believe it’s a way for the server code to “yield” to the client (for example, the browser). However, async and await on ASP.NET only “yield” to the ASP.NET runtime; the HTTP protocol remains unchanged, and you still have only one response per request.

In your case, you're trying to use a web request to kick off a backend operation and then return to the browser. ASP.NET was not designed to execute backend operations like this; it is only a web tier framework. Having ASP.NET execute work is dangerous because ASP.NET is only aware of work coming in from its requests.

I have an overview of various solutions on my blog. Note that using a plain Task.Run, Task.Factory.StartNew, or ThreadPool.QueueUserWorkItem is extremely dangerous because ASP.NET doesn't know anything about that work. At the very least you should use HostingEnvironment.QueueBackgroundWorkItem so ASP.NET at least knows about the work. But that doesn't guarantee that the work will actually ever complete.

A proper solution is to place the work in a persistent queue and have an independent background worker process that queue. See the Asynchronous Messaging Primer (specifically, your scenario is "Decoupling workloads").

Upvotes: 0

Clint
Clint

Reputation: 6220

This seems to be down to a misunderstanding as to what async and await do.

async does not mean run this on a new thread, in essence it acts as a signal to the compiler to build a state machine, so a method like this:

Task<int> GetMeAnInt()
{
    return await myWebService.GetMeAnInt();
}

sort of (cannot stress this enough), gets turned into this:

Task<int> GetMeAnInt()
{
    var awaiter = myWebService.GetMeAnInt().GetAwaiter();
    awaiter.OnCompletion(() => goto done);
    return Task.InProgress;
    done:
        return awaiter.Result;
}

MSDN has way more information about this, and there's even some code out there explaining how to build your own awaiters.

async and await at their very core just enable you to write code that uses callbacks under the hood, but in a nice way that tells the compiler to do the heavy lifting for you.

If you really want to run something in the background, then you need to use Task:

Task<int> GetMeAnInt()
{
    return Task.Run(() => myWebService.GetMeAnInt());
}

OR

Task<int> GetMeAnInt()
{
    return Task.Run(async () => await myWebService.GetMeAnInt());
}

The second example uses async and await in the lambda because in this scenario GetMeAnInt on the web service also happens to return Task<int>.

To recap:

  1. async and await just instruct the compiler to do some jiggerypokery
    1. This uses labels and callbacks with goto
    2. Fun fact, this is valid IL but the C# compiler doesn't allow it for your own code, hence why the compiler can get away with the magic but you can't.
  2. async does not mean "run on a background thread"
  3. Task.Run() can be used to queue a threadpool thread to run an arbitrary function
  4. Task.Factory.Start() can be used to grab a brand new thread to run an arbitrary function
  5. await instructs the compiler that this is the point at which the result of the awaiter for the awaitable (e.g. Task) being awaited is required - this is how it knows how to structure the state machine.

Upvotes: 0

Dmitry Naumov
Dmitry Naumov

Reputation: 727

Dave, you're not forced to use await here. And you're right - from user perspective it still will take 2 minutes. The only difference is that the thread which processes your request can now process other requests meanwhile database does its job. And when database finishes, the thread will continue process your request.

Say you have limited number of threads capable to process HTTP request. This async code will help you to process more requests per time period, but it won't help user to get the job done faster.

Upvotes: 0

Related Questions