In SSRS 2008 R2, I have a query that runs in 5-8 seconds in SSMS. When I run the report it takes about four minutes and when I run the same query in Report Builder's Query Designer it takes about four minutes. (db, query, SSRS, all running locally)
I turned on profiler to capture the activity between the query run in SSMS vs. the query run in SSRS and I see that SSRS puts my query in a string and does an exec sp_executesql command. Is it the dynamic SQL in the sp_executesql that's giving such a dramatic performance difference?
If I change the report's data source to a stored procedure would that eliminate this seeming overhead?