SQL View or Table

Question

I have a table of data that gets update once a week. I then have a query that process this data and essentially returns a list of codes and the amount of hours booked to those codes. This query is reasonably complicated and takes about 5 seconds to run.

This data needs to be used by a lot of other query's in the DB, so I want to put it somewhere that it can be accessed easily by other query's. Doing this in view seemed like a good idea, but that means that every time this view is called it will re-run this query, taking 5 seconds, if there are a lot of calls to this in one go then its going to cause the app to slow down.

So I was thinking, would it be better to create this view as a table when the data is imported on a Monday, as that will be the only time this changes. Is this the best Idea, or am I looking at this the wrong way?

Answer 1

A co-worker pointed me to "Materialized Views".

http://www.pgcon.org/2008/schedule/attachments/64_BSDCan2008-MaterializedViews-paper.pdf

Basically you copy data from a view into a table and uses this table as a view. The point in that presentation (link) is that you can work with triggers and functions to update only some portions of that table.

Very useful, I've implemented such an materialized view in SQL Server.

Answer 2

I deal with the same issues with most of my projects.

We have copious amounts of data which needs to be reorganised for different purposes. We also benefit from a corporate culture that is used to batch jobs and overnight processes, so users are well educated about the snapshotted nature of data. The first thing most users to is export the data to Excel, so it's a non-issue.

Using additional tables is a sensible way to go here.

Personally, I prefix these tables with an underscore.

_LargeUsefulData

This allows me to easily identify convenience tables from entities that play an active role in the normal operations of the system.