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

Sounds like a reasonable way to go about it.

As the query is expensive, putting the results in a "report" table that is to be used by other application sounds like a good compromise.

So long as the users of this data are happy with it changing as you describe, your approach is fine.

Answer 2

You are still "viewing" your rows whether its in a table or in a view of the table rows.

I would first try to optimize your query (hit your indexes, maybe add/update/change your indexes) and run it through a profiler. Profilers provide wonderful insight... into your databases's decision plan.

Answer 3

If your view structure allows indexing it, you can create an indexed view (which is actually just a copy of data updated whenever the underlying tables are updated).

Not every query, however, allows indexing a view over it.

If your data does not to be a second-to-second actual, then creating a table is OK.

Answer 4

Your solution with creating a table to store the data, which get refreshed seems like a good idea.

You could still look to optimise your query, but i would be tempted to go with the table solution.

Answer 5

Yes, it' the first step in the direction of a Data Warehouse :) Of course you should make sure that your new table is always built after every weekly update.

Answer 6

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.

Answer 7

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 8

I have faced similar kinds of issues in my production support where i need to handle a big datawarehouse. Basically, what I did was, I created tables using a shell script. The shell script runs this way:

1. If the temporary table exists,drop it.
2. Run the query by creating a table like create table x as (select ....)
3. Use the temporary table and create as the temporary tables/excels.
4. Drop the temporary table if you don't need it anymore or keep it if you don't think it will eat your disk space.

All this can be done using a simple shell script. I found it's very useful and even now it's running effectively.