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
Sometimes I find it easier to use the most basic debugging tool, which is to add Print statements to the stored procedure with the time it has taken to get there and a statement saying where it is in the code.
It is one way of identifying which queries are causing the delays.
Add this to start of procdeclare @debugdt datetimebefore each query addselect @debugdt=GETDATE()after each query addprint 'finish query N ' + convert(varchar(10),datediff(ms,@debugdt,getdate()))
Then in message tab you get
(1179 row(s) affected)finish query 1 910
(117 row(s) affected)finish query 2 496
(129 row(s) affected)finish query 3 1616
Will drop you an email as am happy to take a lok at the code too
Mark
If the forums had a like button, I would click it on Mark's post. I use print statements all the time to figure out where things are going wrong, both from the perspectives of too much time and unexpected data being returned.
Depending on the report and the tables you are referenceing, using NOLOCK (if you are not already doing that) could potentially shave time, though I doubt it would affect anything to that degree.
John
I usually don't like (NOLOCK) hints since people tend not to realize that it's synonymous with SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; (see Aaron Bertrand's article on the matter), but John raises a really important point -- there aren't a whole lot of reasons for a query to run slower the second time on the same connection, but a "random"-seeming pattern of execution times could indeed be the result of blocking happening as a result of other connections on the server.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
To troubleshoot this, you would run your report until you got one of the "long execution time" runs, and then in SSMS run queries on DMVs or system procedures (or other fun things like Adam Machanic's sp_whoisactive) to find out what your query is waiting on. I'm not an expert on this, so I would recommend that you do some googling on your favorite SQL Server blogger or web resource and see if they can point you in the right direction.
> or other fun things like Adam Machanic's sp_whoisactive
Adding another vote for this. Everyone in the Tess community that is a SSMS user should install this.