Tuesday, March 5, 2013

To embed or not to embed?

Here is a good read on the pros and cons of embedded  in SSRS reports.
http://www.sqlchick.com/entries/2011/4/4/pros-and-cons-stored-proceduresvs-embedded-queriesvs-views-i.html

In a nutshell, the pros to embedding the SQL are:
Pros to developer:
  • No “Create” permissions needed on the source database
  • One-step deployment
Pros to user:
  • None

Now lets look at the pros to using stored procs or views for the datasource.
Benefits to developer:
  • Can reuse the same stored procedure for more than one report, so less code to write in the long run, and fewer bugs to track down in the short run
  • DBA can tune more effectively, if needed
  • Permits the DBMS to secure the object, if needed
  • 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), so less code to write in the long run
  • 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, so easier change management
  • For a minor change, permits the ability to alter the stored procedure without requiring the RDL to be redeployed, so no time wasted redeploying the rdl
  • Can be used for gathering data in applications or smartlists with 100% assurance that the data matches what is on the report.
  • Views give you one more in that they don't need execute permission to run

Benefits to user:
  • Performance: Stored procs can take advantage of quey re-use plans to increase performance.
  • Flexibility: can base ad-hoc reporting (such as smartlist) on the database object that generates the report, rather than having to muddle through table joins.

Now that is a pretty lopsided set of lists.  However, in my opinion, good reports make happy users.  The report is the end product of the user's work, and for many managers, reports are their only interaction with software systems. 

So from that perspective, we should throw away the lists pertaining to developers and just look at the lists of benefits to the users.  That is when we can truly see that stored procs win this battle hands-down.



Did this help you?  If so, please leave a comment!



SQL 2022 TSQL snapshot backups!

  SQL 2022 now actually supports snapshot backups!  More specifically, T-SQL snapshot backups. Of course this is hardware-dependent. Here ...