Hi everyone,
I have an SSRS report that is taking a verrrry long time to load and I'm not sure why. The report has 3 datasets, but they all rely on the same stored proc, which only takes 2seconds to execute in SSMS. Does anyone have any good methods for figuring out which part of the report template is slowing me down?
thanks,
Frannie
Frannie - If it were me, I would open SQL Profiler (SP:Stated, SP: Completed, RPC: Starting and RPC: Ended events) and make sure what you think the report is running is what it is actually running. Verifying that how SSRS calls the stored procedure is not taking your time.
If it isn't actually in the 3 stored procedure calls, then it might really depend on how much data you are sending back to the report and as the other reply here indicates if you are doing a lot of summary data in the report, move it to the stored procedure.
As far as caching, the only caching that I am aware of is in SSRS. When you preview the report with the same parameters a second time it is pulling from the .data file in the report projects folder. You can and should delete that file, in my opinion, so you get a pretty good idea of how long your report takes.
Jesse