1

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

Community
  • 1
  • 1
Bob Tway
  • 9,301
  • 17
  • 80
  • 162
  • Unless the amount of data returned is huge, store procedure wont have ANY impact on your application performance. Store procedure are server compiled TSQL and are interpreted and ran by the server itself. – Franck Apr 08 '14 at 11:28
  • Are you sure it's stalling because of processing power? It could be memory or disc access. – David Apr 08 '14 at 11:29
  • Have you tried monitoring the actual CPU usage while `rep.spCount()` is in-flight, with something like Process Explorer? – noseratio Apr 08 '14 at 13:13

1 Answers1

2

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.

Jeremy Danyow
  • 26,470
  • 12
  • 87
  • 133
  • Yes, that's it, thanks. There's an obvious correlation between the amount of data work different pages are doing and how slow they become. – Bob Tway Apr 08 '14 at 13:19
  • However it looks like the problem is with EF rather than SQL. – Bob Tway Apr 08 '14 at 13:27
  • Hardly. YOu think EF can magically make it's sql go faster? EF interacts with the db only via sql and if that gets slow... – TomTom Apr 08 '14 at 13:37
  • No, it's the way I'm using the context - it's not thread safe - see the question I linked to above. – Bob Tway Apr 08 '14 at 13:38
  • EF doesn't make SQL faster. It slows all calls at a very fast rate. – Franck Apr 08 '14 at 17:21