Pros and Cons: Stored Procedures–vs- Embedded Queries–vs- Views in an SSRS Dataset

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?