CODEWITHSUNDEEP
c#multithreadingstored-procedures
Bob Tway
Bob Tway

Reputation: 9603

Threaded stored procedure stalls whole application

Curious about this. One part of my application calls a stored procedure which can, depending on the circumstances, run relatively slowly.

So I wrapped the call in a thread to enable the users to browse to other parts of the application and do some other work while the SP executes. When the process finishes the RunWorkerCompleted process sends an email to the user, alerting them that the job is done:

bgw = new BackgroundWorker();
bgw.DoWork += new DoWorkEventHandler(
   delegate(object o, DoWorkEventArgs args)   
   {  rep.spCount(); }
bgw.RunWorkerCompleted += bgw_RunWorkerCompleted;
bgw.RunWorkerAsync();

Under conditions which don't cause the SP to be particularly demanding, this works as expected. However, if the SP has a lot of work to do and the user attempts to navigate away while its running, other parts of the app run extremely slowly, often stalling completely, until the thread is finished.

What's puzzling me about this is that I had presumed the heavy lifting was occurring at the database end which is running on a completely separate server - so this thread really shouldn't be demanding much processing power on the user's machine.

So - why does a heavy stored procedure load on the DB server cause the app to stall on a user's local machine and - short of tuning the sproc - is there anything I can do about it?

EDIT: This EF question explains what's going on - I've got a single objectContext and EF isn't very good at handling multiple threads through the same Context. Sensibly, because it causes concurrency issues:

c# working with Entity Framework in a multi threaded server

Upvotes: 1

Views: 241

Answers (1)

Jeremy Danyow
Jeremy Danyow

Reputation: 26406

if the SP has a lot of work to do and the user attempts to navigate away while its running, other parts of the app run extremely slowly, often stalling completely

It's possible while the sp is running other db calls are being blocked or starved for resources, causing unrelated parts of the application to become sluggish.

Try running sql server profiler while attempting to navigate the application while the stored proc is running. Maybe you'll spot a sql statement executing unusually slow due to the sp running at the same time.

Upvotes: 2

Related Questions