Koen Verbeeck (4/9/2014)
Another side effect from SSMS is that it has to display all the rows in the result set. It might do it not so efficiently as SSRS.(just a wild guess here)
Anyway, to really compare you would have to take a look at the execution plan and rule out caching as well.
I'm not sure how to check the execution plan for the procedure when it's called from Reporting Services as I've not looked at that before. Is there an easy way?
Caching is disabled on the reporting, and if I change aspects of the stored procedure the changes are reflected live in the report on a normal report refresh, so I'm confident there's no caching going on.