August 4, 2010 at 8:38 pm
Hi there. I seem to be running into a great argument at my work, and with some other folks I have run into. From the DBA side, it seems that when creating reports in SSRS 2005/2008 there is the belief that using expressions in the report is a bad idea. 'Do as much as you can in the query' seems to be the mantra. Use when or case instead of IIF. Convert in the query etc.
So my question is, does there exist a best practices guide for SSRS? I always try to use expressions, and find that this lends itself to more report functionality.
What do you think, but more importantly, what do the experts say? :unsure:
August 4, 2010 at 9:01 pm
I think the answer is, wait for it....
It depends. What are you doing in the report? Is it concatenating First Name + Last Name in to Full Name or is it aggregation where you will not be drilling down to details? In the first case, I can see doing it in either place, in the second case I would say do it in the SQL.
If it has to do with formatting, put it in the report, if it has to do with data do it in the SQL.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 4, 2010 at 9:08 pm
What I am seeing is all the formatting is being done in the SQL. I wonder what MS says?
August 4, 2010 at 11:19 pm
MaricopaJoe (8/4/2010)
What I am seeing is all the formatting is being done in the SQL. I wonder what MS says?
I agree that "it depends"... but to clarify a bit.
So far as I'm concerned, SSRS is nothing more than a reporting GUI. That means all of the best practices come into play... use stored procedures where ever you can (mostly... everywhere 😉 ) and let the GUI (SSRS) do the formatting so that any localized settings can come into play.
There are, of course, some exceptions... SSRS has "matrices" which are just about as good as a good ol' fashioned cross tab and are just as good as a Pivot. In a lot of cases, Pivoting is NOT done to provide any computational utility. Pivots are many times done for human readability and that means the Pivot is being done for formatting purposes and should be relegated to SSRS (the GUI in this case) to save a handful of clock cycles on the SQL Server. That is, unless the right kind of indexing is done. Like I said, it depends.
The other thing is, (and I say it all the time), "A Developer must not guess... a Developer must KNOW!". If there's a controversy, always remember that "One good measured test is worth a thousand expert opinions any day." In other words, don't waste time on the forums asking which is better... write some code and find out for sure!!! Shoot... it might even be fun if you can get the DBA drunk while you're doing it. :-P:w00t:;-)
--Jeff Moden
Change is inevitable... Change for the better is not.
August 4, 2010 at 11:31 pm
I agree with Jack and Jeff. Do what is appropriate through stored procs and then use the GUI for what it does best.
Expressions oftentimes are very useful in SSRS and SSIS as well.
Test the solution and the reports as best you can and use that as evidence to back your recommendations.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 25, 2010 at 9:53 am
I'm pretty new to RS, but I will suggest one benefit I've found to putting some formatting in SQL and not in RS: maintainability.
I find it much easier and more straightforward to issue an ALTER PROC than it is to fire up BIDS (make coffee, tap fingers...), browse to the correct solution, open the solution, pull up the relevant report, locate the text box(es) where the data is displayed, right-click, edit formula, save, build, deploy..... you get the idea.
If you only change the T-SQL defining a returned field and leave the field name unchanged, it's a much faster edit.
Added benefit -- if that SP is used in 12 reports, say, you make one change, not 12.
This has nothing to do with CPU performance (see Jeff Moden's post about experimenting), but has everything to do with my performance! 😀
YMMV,
Rich
August 26, 2010 at 9:42 am
Just a few of comments on the posts above . . .
1 - It always depends, but you should try to adopt a standard approach, some general guidelines so that the resulting application can then be maintained and augmented in a consistent, easier manner (everyone knows this is where we do 'that')
2 - The solution(s) in BIDS should be version controlled (either manually or using tools), but so should your code in SQL Server (stored proc's, functions, views, table structures, etc). This means that it would be just as involved changing a stored proc as it is changing an expression in a report (although your dev tool might be easier to open, and you probably wont have time to go get a coffee and tap your fingers)
3 - Testing the execution efficiency of a piece of code is not conclusive in determining which is the "best" method, unless execution efficiency is your only measure. You should take into account the amount of time it takes to develop (is it quicker/slower to change an expression in BIDS vs a Stored Proc), who will change the code (DBA time might be more expensive than a developer), who will need to investigate/resolve issues once it's in production (do they have the skills, is it documented enough, etc), will the company be sticking with the same RDBMS and/or SSRS in the near future (will this add complication to a platform migration if they change their db to Oracle, or reporting to Cognos)
The answer "it depends" needs to stretch beyond just determining what you're trying to do with that specific case and look at the bigger picture. If the development team can't resolve it consistently then escalate to someone who has the authority to set a standard and then go with that (as long as they make an informed decision).
HTH
Stephen Robson
August 29, 2010 at 7:20 am
steve.robson 58295 (8/26/2010)
Just a few of comments on the posts above . . .1 - It always depends, but you should try to adopt a standard approach, some general guidelines so that the resulting application can then be maintained and augmented in a consistent, easier manner (everyone knows this is where we do 'that')
2 - The solution(s) in BIDS should be version controlled (either manually or using tools), but so should your code in SQL Server (stored proc's, functions, views, table structures, etc). This means that it would be just as involved changing a stored proc as it is changing an expression in a report (although your dev tool might be easier to open, and you probably wont have time to go get a coffee and tap your fingers)
3 - Testing the execution efficiency of a piece of code is not conclusive in determining which is the "best" method, unless execution efficiency is your only measure. You should take into account the amount of time it takes to develop (is it quicker/slower to change an expression in BIDS vs a Stored Proc), who will change the code (DBA time might be more expensive than a developer), who will need to investigate/resolve issues once it's in production (do they have the skills, is it documented enough, etc), will the company be sticking with the same RDBMS and/or SSRS in the near future (will this add complication to a platform migration if they change their db to Oracle, or reporting to Cognos)
The answer "it depends" needs to stretch beyond just determining what you're trying to do with that specific case and look at the bigger picture. If the development team can't resolve it consistently then escalate to someone who has the authority to set a standard and then go with that (as long as they make an informed decision).
HTH
Stephen Robson
I can't agree with #3, Stephen. Performance is second only to accuracy and there's no reason for performant code to be difficult to maintain. I do agree that some folks waste time "tuning" code to run a million rows in 1 second instead of 2 but writing code that takes 2 seconds instead of an hour should always be a goal. Such achievements can easily be accomplished simply by writing code that follows best practices. It's just not difficult.
If you take a look at many of the questions on this forum, you'll find thousands that complain of slow code. It's obvious that, as you suggest, "testing the execution efficiency of a piece of code" wasn't real high on their list of priorities, either. The problem is that such performance problems always raise their ugly heads when you can least afford them to and they cost a whole lot more to fix than to simply avoid to begin with.
Performance should always be a consideration for any and all types of code.
"Make it work, make it fast, make it pretty... and it ain't done 'til it's pretty." --Jeff Moden
--Jeff Moden
Change is inevitable... Change for the better is not.
August 29, 2010 at 8:55 am
#3 is about striking the right balance of all the facets involved - not just focusing on performance for the sake of it; good enough is good enough. Spending 100hrs of effort trying to performance tune a piece of code that runs a few times per week (or in some cases a few times per annum), only makes sense if it needs to run more quickly. This is especially important when that piece of code is a small part of a much bigger solution and there are other components that need that 100hrs of effort to complete. When it's good enough, stop trying to improve it. A second or two of improvement only matters if that piece of code is going to be executed many times, milliseconds of improvement will only matter if the code is to be run 000's or 00'000's of times in a day. It's all about getting the right balance of effort vs improvement vs requirements. I'm not arguing that performance is important - it is, it should be planned into the design/development/testing right from the initial requirements phase - long before any developers are involved.
It's great if you can get a report to run in 20 seconds instead of 10 minutes, but if all that time you spent on that means you didn't get the other 9 reports done, then you've only delivered on 10% of the requirements. Now consider that that particular report is only produced 1/week for a weekly planning meeting of some kind and 10 minutes was good enough because some admin would run the report, go get a coffee, come back and print out 20 copies for the meeting. If the report was to be executed several times/day and was embedded into an operation process, then it would make sense to spend the time to get it to execute in the amount of time specified in the requirements.
It's all about balancing how much time you spend on different tasks compare to how much time you should spend on them.
September 4, 2010 at 11:43 am
steve.robson 58295 (8/29/2010)
#3 is about striking the right balance of all the facets involved - not just focusing on performance for the sake of it; good enough is good enough. Spending 100hrs of effort trying to performance tune a piece of code that runs a few times per week (or in some cases a few times per annum), only makes sense if it needs to run more quickly. This is especially important when that piece of code is a small part of a much bigger solution and there are other components that need that 100hrs of effort to complete. When it's good enough, stop trying to improve it. A second or two of improvement only matters if that piece of code is going to be executed many times, milliseconds of improvement will only matter if the code is to be run 000's or 00'000's of times in a day. It's all about getting the right balance of effort vs improvement vs requirements. I'm not arguing that performance is important - it is, it should be planned into the design/development/testing right from the initial requirements phase - long before any developers are involved.It's great if you can get a report to run in 20 seconds instead of 10 minutes, but if all that time you spent on that means you didn't get the other 9 reports done, then you've only delivered on 10% of the requirements. Now consider that that particular report is only produced 1/week for a weekly planning meeting of some kind and 10 minutes was good enough because some admin would run the report, go get a coffee, come back and print out 20 copies for the meeting. If the report was to be executed several times/day and was embedded into an operation process, then it would make sense to spend the time to get it to execute in the amount of time specified in the requirements.
It's all about balancing how much time you spend on different tasks compare to how much time you should spend on them.
I agree on the balance thing and we apparently disagree on most everything else. I'm saying that if someone can't write a report correctly and to run in a whole lot less than 10 minutes without much effort, then they probably don't know enough to do the job correctly to begin with. I'm also saying that performance is just as important as accuracy and that good programmers can do both without have to spend hours and hours of tweaking time just to get a report to run.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 5, 2010 at 12:04 am
The original post was made in the hope that there is some 'recommended practices' from Microsoft. Has anyone see any such white paper?
September 5, 2010 at 1:21 pm
MaricopaJoe (9/5/2010)
The original post was made in the hope that there is some 'recommended practices' from Microsoft. Has anyone see any such white paper?
I don't see the word "Microsoft" anywhere in nor even inferred in the original post but this should suffice for the current question. 😉
http://msdn.microsoft.com/en-us/library/cc966445.aspx
--Jeff Moden
Change is inevitable... Change for the better is not.
September 5, 2010 at 4:00 pm
MaricopaJoe (9/5/2010)
The original post was made in the hope that there is some 'recommended practices' from Microsoft. Has anyone see any such white paper?
I am not doing SSRS now but when I did all my mile long reports are written in Stored Procs appended with rpt. I think Asp.net skills is more useful than expression because SSRS pages are web pages even in Winform applications.
Kind regards,
Gift Peddie
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply