Querying a view vs tables in SSRS Dataset

Question

Is there a best practice when it comes to using Datasets in SSRS? Should I be writing a query for the dataset that pulls directly from tables, or should I be creating a view in the source database and then querying the view via the Dataset?

I understand there are some scenarios where the view would be better (centralizing a view for use in multiple reports) and some scenarios where a query of the tables would be better (if you are not allowed access to create views in the source DB or if you utilize multiple DB data sources in one report). But are there any performance implications? Can the server still cache the results of the dataset query from SSRS in a similar fashion as it would the results in a view?

Any insight will be appreciated :-)

Answer 1

It depends.

Using a view insulates you from data model changes - the tables can change but as long as you can get the data out as it was before, things are fine. A view is just a SQL statement, it's like running a subselect. Because of the encapsulation, there's more risk that the underlying query would be more inefficient. Using a materialized view (indexed view in SQL Server) is an option to make a query faster, but the requirements are quite limiting.

If you see the need for the same information across numerous reports - yes, use a view. Otherwise query the table directly.

Either way, do it via stored procedure...

Answer 2

I generally use stored procedures for my reports, so that way any code can be reused (and consistant). Also, you can then apply filtering in the sp, which decreases your load time.

Answer 3

And I would caution you against using views that reference other views. I have seen these create performance problems.