Hi all,
I've written a few custom reports and for the most part they all work pretty well. I have one report though that is having some serious speed issues. To be fair, it's a pretty crazy report with many calculations and I wouldn't expect it to render immediately. That said, the generation time for the report take anywhere from 2 seconds to 15+ minutes with the exact same parameters and running it back to back. Literally. I've executed it in SSMS and after it completes I'll run it again exactly the same and it'll take a completely different amount of time. Funny enough, it often goes from running very quickly to running very slowly (which is the opposite of what I'd expect).
It is a complete gamble any time anyone runs the report. They either get it immediately or it times out before the report even renders for them. In the meantime, I've been running it for them directly out of SSMS and giving them the output that way. It's not ideal for anyone.
Since the speed issue also affects me in SSMS, I'm thinking that the issue must be with the stored procedure itself and not with the SSRS report face. I'm not sure how to make it more efficient though. I've tried looking at the Execution Plan but all it's telling me is to put in indexes where they already exist.
Do any of the reporting writing gurus have advice or tips for how they improve efficiencies in their own codes? I'm happy to show you what i've been working on offline if you contact me directly. I know it's not perfect but I'm trying! Still have lots to learn.
Thanks for your thoughts!
Beth
bhawryluk@winspearcentre.com
You guys are all so smart and fantastic! I love it.
I'm going to take a look at your suggestions and see what I can figure out. Lots of new things to learn!