Hoping some of you server-savvy types can help me out.
I have a report that is running slowly. I keep tweaking the SQL to improve the efficiency of the query. Seems to work great. I make the changes and run the report 3 or 4 times. Render time drops from 5 minutes to 40 seconds. Great!
An hour later, it's taking 5 minutes again without any additional changes having been made.
When I run the query from Management Studio it is plenty fast. Using the same parameters, I get results in 10 seconds. From BIDS or from Tessitura, it is several minutes.
Where should I be looking for the bottle neck?
Hi Levi,
Have you used SQL Profiler while running the report in SSRS? If not, I would suggest doing that using the Tuning template (or whatever template/configuration you like to use for such things). You should be able to pinpoint whether it is your query or something else that is making it run long. That would be the first thing I would be checking if I ran into something like this.
Thanks,David
David,
Profiler is one of my favorite tools. Unfortunately with SSRS reports, I'm not able to see the actual SP execution as I could with Infomaker. Now all I see is an execute statement for LP_PREPROCESS_URL at which point everything is handed over to the SSRS engine and I don't see the subsequent calls being used in the report. We are on RAMP, so I'm assuming that those calls are happening on a server I can't see. But it is a frustration.
I'm sure this is a problem with how SSRS is rendering the data it's getting. The query itself is plenty fast (I think). But I'm not familiar enough with what's under the hood of SSRS to know what might be causing a performance issue there.