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
Hi Beth,
You might be dealing with parameter sniffing when SQL Server creates the execution plan for this stored procedure. In a nutshell, what SQL Server tends to do is build a query plan when a stored procedure is first excuted and then it caches that plan for future use. That might work great for some parameter values, but might be a horrible plan for other values. It can result in the behavior you are describing.
One way to test to see if this is possibly impacting you is to use WITH RECOMPILE; that will tell SQL Server to build a fresh plan each time the procedure is run. After you add that, try some of your tests in SSMS after adding this to see how that impacts your results.
If it helps, you can keep WITH RECOMPILE, but there could be a performance hit due to regenerating the plan each time. If this procedure is run many times throughout the day, then you might want to target the parts of the stored procedure that are taking the longest with query hints, such as OPTIMIZE FOR.
This article describes some of those strategies: https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/
I hope this helps and good luck!
Thanks,David