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

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

Answer 3

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 4

If performance is really important (and if the DBAs will allow it) consider actually creating a table - not a view - for every report report.

Create a stored procedure to update the tables in the nightly refresh. The data set in your report will be a simple

select *
from rptRydmansTable
where Column1 = @Parm1 and Column2 = @Parm2

These things fly!