View vs Table in SQL Server [closed]

Question

TASK: for the dashboard purpose, I have a complex select query with lot of joins and sub queries which get the data from multiple tables.

OPTION 1: I can create a view with that SELECT query and Spotfire can use that view as the data source for its dashboard.

OPTION 2: I can create a SSIS package by using that SELECT query as a source and I can load the data into a single table ONCE PER DAY. Spotfire can use that single table as a source for its dashboard.

NOTE: I don't want it REAL TIME. Dashboard can update every night (once per day).

My understanding: I know that the view is basically a stored SELECT query. If I create a view, every time when a user accesses the Dashboard, it will try to access the view. That means, in the back end view will hit the underlying tables in the database. Instead of this, I can simply have specific table loaded once per a day by SSIS. SO that Dash will touch this table instead of the actual highly transactional tables.

Can you suggest me with an option along with explanation?

Answer 1

You seem to have covered most of the issues.

The advantage to the View is that it's real-time, as of the moment the report is run. The disadvantage is that it's live load on your system, including not only CPU, RAM, and disk I/O but also (often more importantly) table and row locks.

The advantage for the SSIS package is disconnecting your reporting load from your production load (again: especially the reduced locking contention is a win here), and being able to create special reporting-only indexes to make your reports run faster without necessarily needing to maintain these indexes as changes come in for live tables (especially since the indexes useful for reporting are often very different than the indexes useful for production updates). The disadvantages are increased complexity and failure points (what if the SSIS package starts failing?), stale data, and the fact that if you're just moving to a special table on the same server the reports still use some CPU, disk, and RAM resources from your production server, even if it is reduced.

Personally, I tend to prefer using Views, right up until the point where I don't :) .

By this I mean once I start using SSIS packages to move data to a separate location for reports, I want to go "all-in" on that process. Don't just do one report or table on the same server; get a separate reporting server (or even a full-fledged data warehouse solution) and move as much as possible over to it. Then make sure someone is responsible for monitoring it's health and the health of the migration packages. I'll also start working to change the corporate culture so everyone is comfortable seeing yesterday's data. But I want to put this off for as long as I can, and stick with Views until I start seeing a tangible reason to move to some kind of real data warehouse or analytics product that will justify this cost.

Answer 2

To some extent, this is a matter of opinion. But, you have defined the following situation:

• Dashboard is only updated once per day, so it should be constant during the day.
• Underlying tables are being used for transactional purposes.
• Required query is at least somewhat complex.

Combined, these strongly argue for creating a dashboard table in the middle of the night, to simplify the querying.

In many environments, the underlying transactional tables are not used directly for reporting. Instead the tables are copied to a reporting environment (either periodically or in realish-time) and the reporting environment is used for reporting.