1

I have a stored procedure that I know might take some time to execute.

When I run it from SQL Server Management Studio, it takes about 12-15 minutes to complete and that is fine.

But when I run it from my C# asp.net web application it crashes after 40 minutes and it still doesn't complete.

What it the difference between the two runs and how can I fix it to run from my application?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Liran Friedman
  • 4,027
  • 13
  • 53
  • 96
  • 5
    **[Slow in the Application, Fast in SSMS? Understanding Performance Mysteries](http://www.sommarskog.se/query-plan-mysteries.html)** Parameter sniffing or concurrent access and blocking – Lukasz Szozda Dec 27 '15 at 13:48
  • Have you tried capture times using Profiler? – shadow Dec 27 '15 at 13:54
  • Yes, I did but it didn't help that much... – Liran Friedman Dec 27 '15 at 13:56
  • Maybe something in connection string? – shadow Dec 27 '15 at 14:15
  • are you sure it is crashing on the stored procedure call OR is it possibly crashing afterwards (once you retrieved the data from SQL)? What are you doing with the data after (any loops)? – Qudoos Dec 27 '15 at 14:49
  • The case is simple, I just call the stored procedure from my code and it runs for 40 minutes and not all rows it supposed to update are updated, and when I run it from the SQL studio it runs for 15 minutes and all rows are updated. – Liran Friedman Dec 27 '15 at 19:46
  • Are you really having a stored procedure that runs for 15 minutes? With that being said, have you tried using profiler to see how the procedure is being called from the app, there must be something different. – Apostrofix Dec 28 '15 at 10:19
  • The 15 minutes are not an issue because it does a lot of actions on a lot of records. We've tried using a profiler, but it didn't point to any issue – Liran Friedman Dec 28 '15 at 10:22
  • @LiranFriedman There must be an issue and hence the difference in time. Have you tried to recompile the stored procedure? Have you also seen that: http://stackoverflow.com/questions/1265259/sp-taking-15-minutes-but-the-same-query-when-executed-returns-results-in-1-2-min – Apostrofix Dec 28 '15 at 10:33
  • @LiranFriedman Great, I hope that it will help you solve the problem :) – Apostrofix Dec 28 '15 at 12:26

2 Answers2

1

To solve the crash we've increased the timeout in the IIS. @Apostrofix has posted this link with an unexpected solution, that I wasn't familiar with before.

A quote from this post is:

The recommended fix for most situations (though it depends on the structure of your query and sproc) is to NOT use your parameters directly in your queries, but rather store them into local variables and then use those variables in your queries.

Community
  • 1
  • 1
Liran Friedman
  • 4,027
  • 13
  • 53
  • 96
  • That recommended fix (storing parameters into local variables) made the execution time of my SP drop from 4'30'' to 15'' !! – Daniel May 20 '16 at 10:21
0

Hey you can try increasing the timeout from connection string in your web config : just change the value of timeout key.

<add key="Inf:DatabaseCommandTimeoutSeconds" value="20" />
Suman Pathak
  • 300
  • 1
  • 8
  • I know I can increase the timeout, but I don't want the query to run that long, it is a bad workaround to do so... – Liran Friedman Dec 28 '15 at 10:16
  • Can you paste your database query?? and tell me how much data are you working with? Maybe i can optimize the query. – Suman Pathak Dec 28 '15 at 10:21
  • Ok. So you can look into to optimize your query yourself. Use "Include Actual Execution Plan" option in SSMS for viewing time used by your each part of query. – Suman Pathak Dec 28 '15 at 10:35