title: Stored procedure slow when called from web, fast from Management Studio
body: <p>I have stored procedure that insanely times out every single time it's called from the web application.</p>
  
  <p>I fired up the Sql Profiler and traced the calls that time out and finally found out these things: </p>
  
  <ol>
  <li>When executed the statements from within the MS SQL Management Studio, with same arguments (in fact, I copied the procedure call from sql profile trace and ran it): It finishes in 5~6 seconds avg. </li>
  <li>But when called from web application, it takes in excess of 30 seconds (in trace) so my webpage actually times out by then. </li>
  </ol>
  
  <p>Apart from the fact that my web application has its own user, every thing is same (same database, connection, server etc) 
  I also tried running the query directly in the studio with the web application's user and it doesn't take more than 6 sec.</p>
  
  <p>How do I find out what is happening? </p>
  
  <p>I am assuming it has nothing to do with the fact that we use BLL > DAL layers or Table adapters as the trace clearly shows the delay is in the actual procedure. That is all I can think of.</p>
  
  <p><strong>EDIT</strong> I found out in <a href="http://aspadvice.com/blogs/ssmith/archive/2008/02/15/Stored-Procedure-Performance-Varies-Between-ADO.NET-and-Management-Studio.aspx" rel="noreferrer">this link</a> that ADO.NET sets <code>ARITHABORT</code> to true - which is good for most of the time but sometime this happens, and the suggested work-around is to add <code>with recompile</code> option to the stored proc. In my case, it's not working but I suspect it's something very similar to this. Anyone knows what else ADO.NET does or where I can find the spec?</p>