title: SSRS report from URL taking a huge amount of time
body: <p>I have a stored procedure retrieving a large amount of data (two hundred thousand records).
  The SP works fine in SSMS with a run time at around 12 seconds.
  I am running an SSRS report with the SP, which works fine in SSRS preview, it takes around 30 seconds.
  When I run the SSRS Report via the SSRS URL access it hangs for about half an hour before crashing with a</p>
  <blockquote>
  <p>Sys.WebForms.PageRequestManagerServerErrorException: An unknown error occurred while processing the request on the server. The status code returned from the server was: 500</p>
  </blockquote>
  <p>Although the SP crunches a lot of data it only returns a small amount to the report (circa 200 rows max).</p>
  <p>I have ensured that my SP has local parameters and all input parameters are passed through these for use (which I believe helps with parameter sniffing?).</p>
  <p>I have checked that the SSRS preview is not retrieving cached data by clearing down the cache.</p>
  <p>I have tried <code>DBCC DROPCLEANBUFFERS;</code> and <code>DBCC FREEPROCCACHE</code> to see if that makes a difference.</p>
  <p>I checked log using <code>Select * From Executionlog2</code> and found that the <code>TimeDataRetrival = 32550</code>, <code>TimeProcessing = 29</code> and <code>TimeRendering = 1798778</code>.</p>
  <p>It looks to me like the report is taking ages to render rather than retrieving data, which given the low number of rows passed to the renderer is strange.</p>
  <p>Has anyone got any ideas what my next course of action/investigation should be?</p>