SSRS: Slow Report over a Stored Procedure
While working on a report based on the results of a complex stored procedure I encountered what I considered to be a strange behavior. I noticed that when the stored procedure was executed directly the results were returned in seconds and they were returned in several minutes when executed from the report.
After further looking into the execution I realized that the parameters in the report were “prefetched” and analyzed where they were used in the procedure. which took some time. I made a few small changes to the procedure and had favorable results. I declared local variables, assigned them the values that were passed to the procedure and used the local variables within the procedure for filtering the data. The report results were returned in the same interval as executing the stored procedure directly.
