SSRS Consultant Tip: Stored Procedures vs. Views for SSRS Reports

I hope I’ve convinced you in my previous post to develop and store your SQL on the database and my rationale for that argument.

The next question is how to create and keep this code on the server. You have lots of options, such as views, stored procedures, functions, simple data marts and even SSAS.

In this analysis, I’m going to focus on views and stored procedures. That’s where I spend most of my time and is most relevant for “next level” super users.

Generally, I like to start with views before going to stored procedures. I do this for two reasons:

Almost every reporting tool I’ve ever used makes it easy to use views. After all, views look just like tables to a reporting tool. The same can’t be said for stored procedures.

For example, if you connect to SQL Server from Excel, you’ll see views and tables, but no stored procedures:

So save yourself some trouble and go with views wherever you can.

At Red Three, we often create a basic view that we can then combine with many other views.

For example, we often work with Dynamics GP and NAV. Both systems have situations where the data is stored in an entity-attribute-value format, which is less then ideal for reporting. In Dynamics GP, for example, additional customer data may be stored in Extender while in GP additional customer data may be stored in Dimensions.

To deal with this problem, we’ll create a master customer view of this data and then “normalize” it so we can then combine that view with many others.

(I acknowledge that this can become too much of a good thing. Combining too many views can produce a mess of unsupportable code.)

All that said, I do still use stored procedures regularly. Sometimes, a report will require multiple steps or a calculation that can’t be handled in a view.

You may have heard that stored procedures are faster than views, and therefore preferable. (A consultant once told me that stored procedures are ALWAYS faster than views.) It’s true that a stored procedures doesn’t need to be compiled (as views do). And I suppose that this extra time might be meaningful if a report is going to run thousands of times.

However, I live and breath mid-market companies, and this difference in processing speed is NOT A FACTOR in performance in my universe. (At some point in the future, I am going to spend some time talking about important performance differences. But this isn’t one of them. For an example of something that does matter, see “SSRS Tip: Put Parameters in Your Query, Not Your Filter.”)