0

I've got a report that does some heavy SQL queries; it can take over half an hour to run. So I've written it as a stored procedure that saves data to a table with a report key supplied by the client, and a status field indicating if the report is still in progress or complete. Now the client can invoke an API, which spins off a task thread, and the client can keep polling the API to see if the report is complete, then download the results. In other words, something like this:

public IHttpActionResult RunReport(ReportParameters parameters) {
    Task.Run(() => _reportService.RunReport(parameters));
    return Ok();
}

Trouble is, it seems that after a certain amount of time, the thread gets garbage collected; the spid running the report dies and the report status never gets updated.

I saw this question & answer which seems to relate to my issue. The API is continually active, not least because the client is checking back every 5 seconds to ask "Is it ready yet? And now? And now?" So it would seem that the thread shouldn't get garbage collected...but I can't argue with what I'm seeing, which is that the stored procedure simply dies in the middle, without raising any exceptions.

I could use Hangfire, of course, but that has its own overhead, and more moving parts to manage. Is there some other way to ensure the report thread doesn't die?

Shaul Behr
  • 36,951
  • 69
  • 249
  • 387
  • 2
    If you have processes that run for such a long time, don't do them in a single action method like this. Throw the processing out to a queue or a background job system like Hangfire. Let the user start the report then get a notification later that it's been done and a link to download the report. – DavidG Jul 31 '17 at 08:29
  • Honestly this code seams like a terrible idea either write your own run and forget engine or use a library you will end up regretting it if you don't. – Filip Cordas Jul 31 '17 at 08:45
  • As @DavidG said, anything that runs for a long time should be done in a different process and the user notified when complete. – matt_lethargic Jul 31 '17 at 08:51

1 Answers1

0

So I found that the reason my thread was dying was because the timeout on the SQL command was too short. D'oh! Extended the timeout and the thread doesn't die any more.

Nonetheless I take the points made in the comments that for more reliability it would be better to use a framework like Hangfire. For the time being, though, setting the timeout appropriately is good enough.

Shaul Behr
  • 36,951
  • 69
  • 249
  • 387