Pros and Cons: Stored Procedures–vs- Embedded Queries–vs- Views in an SSRS Dataset
Monday, April 4, 2011 at 9:14PM Overview: This blog entry lists considerations for deciding how to handle data access for an SSRS report.
Recently someone at my office asked, “Do I really need to create a stored procedure for a really tiny, simple, SSRS report query?” Great question. In the past we had taken the position of doing all SSRS data access via stored procedures. But…should we? As with everything we do, there’s no simple black & white answer.
Below are some things to consider when reporting from a relational data source. When making the decision, ideally you can get input & agreement from both the DBAs and the Developers.
Stored Procedures as a Dataset Source for an SSRS Report
Pros
- Performance: the ability for the stored procedure to reuse a query plan (stored procedure cache)
- Can reuse the same stored procedure for more than one report (may minimize errors or duplication)
- DBA can tune more effectively, if needed
- Permits the DBMS to secure the object, if needed
- Centralization of queries into views is often preferable to DBAs because the queries are more transparent to them
- Provides a layer of abstraction between the database tables & the report (for example: you can alias column names, create derived fields, minimize the effect of other physical object changes, or show less fields to simplify the reporting process)
- Provides an additional layer of security since "Execute" permissions are required for either the user running the report, or an impersonation account
- Ability to query system tables to find usage of tables & columns (which may help with change management)
- For a minor change, permits the ability to alter the stored procedure without requiring the RDL to be redeployed
Cons
- Need “Create” permissions on the source database (as an outside consultant, this is not always granted), or need another person to create the stored procedure for you
- Slightly more knowledge is required to create a stored procedure than a simple select statement
- Can clutter up the database with quite a few simple queries and/or redundant queries
- Additional handling is needed to parse multi-valued parameters in SSRS
- Two-step deployment (the stored procedure, and/or the RDL); this creates an opportunity for error if not deployed concurrently
- Additional testing of a changed stored procedure & the effect of the change on the report (which may take slightly more time since they are separate)
- May require additional personnel / time / coordination of efforts if the stored procedures are maintained & enhanced by staff other than the reports (for example, if a field changes, or a new parameter is requested)
Embedded SQL in SSRS Dataset
Pros
- Easy (less syntax for a beginner to learn)
- No “Create” permissions needed on the source database
- One-step deployment (unless you are using a Shared Dataset, which is stored outside of the RDL)
Cons
- For large datasets, may not perform as well as a stored procedure
- Greater possibility that individual report queries are redundant or handling logic in different ways (a great way to combat this is to use Shared Datasets, a new feature in SQL Server 2008 R2)
- SSRS Query Designer doesn’t retain formatting well
- SSRS Query Designer removes comments (a big shortcoming in my opinion)
- SSRS Query Designer renames aliases in some circumstances
- The report developer may need additional permissions to database objects to construct the queries (i.e., if direct table access is being utilized instead of views)
- Need to open the report in BIDS (or Report Builder) in order to make a change
- Much more difficult to monitor the database objects being accessed by these queries (i.e., not as easy as querying the system tables with a stored procedure)
Views as a Dataset Source for an SSRS Report
Usage of Views may be done in conjunction with either Stored Procedures or Embedded SQL, discussed above.
Pros
- Can reuse the same view for more than one report (which facilitates the coveted ‘one version of the truth’ and may minimize errors or duplication)
- Particularly helpful when exposing fields for end-user ad-hoc reporting (i.e., can create a “friendly” list of fields, with related fields pre-joined)
- DBA can tune more effectively, if needed
- Permits the DBMS to secure the object, if needed
- Centralization of queries into views is often preferable to DBAs because the queries are more transparent to them
- Provides a layer of abstraction between the database tables & the report (for example: you can alias column names, create derived fields, minimize the effect of other physical object changes, or show less fields to simplify the reporting process)
- Ability to query system tables to find usage of tables & columns (which may help with change management)
Cons
- May have some query plan reuse issues
- Limitations when compared to Stored Procs (ex: cannot use a variable within a view)
- Auto-joins don’t always work seamlessly in report creation tools (i.e., if foreign key constraints are enabled on the tables, auto-joins are often done for you; with views the best you’ve got is field names which match)
- Need “Create” permissions on the source database (as an outside consultant, this is not always granted), or need another person to create the view for you
- Slightly more knowledge is required to create a view than a simple select statement
- Two-step deployment (the view, and/or the RDL, or both); this creates an opportunity for error if not deployed concurrently
- Additional testing of a changed view & the effect of the change on the report (which may take slightly more time since they are separate)
- May require additional personnel / time / coordination of efforts if the view is maintained & enhanced by staff other than the reports (for example, if a field changes, or a new parameter is requested)
Conclusion
We concluded that the critical reports will continue to be driven by stored procedures. However, we also determined that smaller, less critical reports (such as a set of internal IT audit reports) will be permitted to have embedded queries for the sake of simplicity & to save time.
Views are used significantly in the facilitation of ad-hoc reporting for end users. (As a sidenote: we also utilize views as the source for Analysis Services.)
Personally, I like to have a line drawn in the sand at the BIDS project level: if a set of reports is being deployed to destination ABC, then I know that whole set of reports in the project will have its data access handled the same way. What I don’t want to do is have to look at each individual report to figure out if its query is in a stored procedure or embedded in the SSRS report.
Finding More Information
Adam Haines Blog: SSRS – Should I Use Embedded TSQL or a Stored Procedure?
Reader Comments (7)
Hi Melissa,
That's a great list you've put together -- the most thorough I've seen on the subject. Well done.
I would add that SSRS Query Designer doesn't simply abandon your formatting and comments, it will go so far as to rename objects in your query if you've aliased them (CTE's for example). I agree that it's better to have stored procs in critical reports. I lean toward SP's by default, with only the most elementary queries excepted.
Thanks for blogging about this!
-Doug
I feel you've unduly criticised embedded T-SQL because you're either working with extremely large enterprises or companies that have compliance/legal requirements that require dedicated consultants to advise on; and Adam is even more out of touch by dealing with corporations that have dedicated DBAs to create, inspect, manage, troubleshoot, and maintain stored procedures and database security.
From the low-to-mid end of the market it's my experience that:
- Assumptions that end-users or anyone not trained in SQL or SSRS will ever write or modify reports, are wrong.
- Assumptions that a high-end DBA or consultant will properly implement security or monitor database access, are wrong.
- Effort put into optimisations aside from those that improve where a single user is running a single report, are wasted.
So I challenge your bullet points as follows.
> Easy (less syntax for a beginner to learn)
> SSRS Query Designer doesn’t retain formatting well
> SSRS Query Designer removes comments (a big shortcoming in my opinion)
> SSRS Query Designer renames aliases in some circumstances
Irrelevant because only professionals write reports, and professionals don't use the query designer. (And I wouldn't call the syntax "easier" from a stored procedure when the difference is a few lines of boilerplate code).
> For large datasets, may not perform as well as a stored procedure
Irrelevant because it's a fraction of a second.
> Greater possibility that individual report queries are redundant or handling logic in different ways (a great way to combat this is to use Shared Datasets, a new feature in SQL Server 2008 R2)
Irrelevant, duplication can occur between stored procedures too.
> The report developer may need additional permissions to database objects to construct the queries (i.e., if direct table access is being utilized instead of views)
Irrelevant, because a report designer either needs similar access (unless you're working from a printed database plan!) to construct the stored procedure, or you're working in an enterprise so large that DBAs have to do it for you; in which case you'll need the kind of dozen-page requirement sheets that rarely exist in the rest of the real world.
> Need to open the report in BIDS (or Report Builder) in order to make a change
Irrelevant, because if you changed a stored procedure you would run the report anyway to make sure the change didn't inadvertently affect something else.
> Much more difficult to monitor the database objects being accessed by these queries (i.e., not as easy as querying the system tables with a stored procedure)
Irrelevant, because nobody looks or cares (even in a huge enterprise, I suspect few have enough time on their hands to do such a thing, or have a reason to).
I would really love to walk in your shoes for a day and see what it is like though! Different worlds, different worlds.
Cody -
Love the feedback! I don't disagree with anything you've said - for a small organization, I would completely agree that you should make things as simple as possible. Since I'm currently in consulting I do tend to work with larger organizations, so you're right on the money that some of the above items wouldn't be things you'd be concerned about. (And, granted, some of them are not big deals - I was merely trying to be thorough.)
Just one thought on your very last comment...just last week I ran a query to find how many stored procs or functions utilized a particular column, because a column name in a table was going to be changed (and we didn't have views in between). Of course we could search the RDL's XML files for the same thing if the queries were embedded, so not a huge deal.
Thanks for reading & for commenting. Have fun in your world!
Melissa
Hi Melissa,
Nice Article !!!
I would like to add one more point to the pros of using Stored Procedures (SPs) and that is "Use of SPs provides an additional layer of security, since SPs require explicit EXECUTE permissions for the user running the report if impersonation is not used in the report data source and explicit EXECUTE permissions for the account used to access/run the SP if impersonation is used in the report data source".
Keep up the good work !
Regards,
Datta
Datta,
Thanks very much for the suggestion - I've added it to the Pros list in the entry.
Melissa
We have implemented a custom footer for all reports than includes a drill through to a "Report Info" report. This report queries (via a stored proc!) the report server database and has all of the stored procs and any embedded queries used in the report listed along with the data sets used, master report if it is a linked report and other key data like when it was last updated on the report server and by who. This is a means of easily accessing some key "documentation" about the report that is dynamically generated without every leaving the browser.
Ted,
Very nice idea - I love incorporating documentation within the solution. Have you found a good way to implement report metadata re: SSRS report version #s incrementing, and/or who changed the SSRS report & why? If so, how do you maintain that metadata? Do the report developers need to enter it?
Thanks for reading!
Melissa