SSRS & Stored Procedures
As an ETL Monkey, my experience with reports has been a bit incidental. One thing that puzzled me though is why report developers always wrote stored procedures to generate data for their reports, instead of using the SQL capabilities within the report. They said “Best Practice” and I was happy to leave them to it!
In this detailed post Adam Haines explains why. It’s very detailed so here are the key points if you have a short attention span:
- The query can be maintained independently of SSRS, allowing tuning the query without accessing or modifying the reports
- The execution plans can be cached if you use an Stored Procedure, but not if you use SSRS
- Stored procedures allow the use of certain objects that cannot be used in embedded T-SQL in the report such as temp tables and indexes specific to those temp tables as well as table variables
- Stored Procedures provide a layer of abstraction between the report and the business logic
- Stored Procedures allow re-use of similar logic
Credit for the above list to my colleague John Simon who authored most of the above points in an internal discussion.
AUSSUG Upcoming Sessions
In case you aren’t in the Australian SQL Server User Group, AUSSUG, there are a few upcoming sessions in Sydney which will be pretty useful – check out the official site to register. Sessions are free and always useful.
Lunchtime Wed 3rd March, 2010 – Ensuring Optimal Performance in SQL Server 2008 Based Applications with Viktor Isakov
Evening * Thu * 4th Mar 2010 – What’s new in Reporting Services 2008 R2 and PerformancePoint Services 2010 with Peter Myers, presenting what’s new in the upcoming release of Reporting Services 2008 R2 and PerformancePoint Services 2010
And TBA date in April 2010 – Knights of the SSIS Round Table – Kevin Wong, Glyn Llewelyn and myself will be presenting a series of mini demos followed by Q&A, so a chance to pick some expert brains
Hope to catch you at one of the sessions!