x

Any advantages of creating a table from a view?

(...I found some discussions on advantages of selecting from a view VS from a table, but did not find anything on advantages of selecting from a table VS a view...) Here is my story: a daily running SSIS package has the following SQL:

DROP TABLE xxx; SELECT * INTO xxx FROM view_xxx

The result is a multi-million rows table recreated daily. The latter statement executes for several hours and every time causes the TempDB to double its size. So, it's a problem. And it feels rather unusual, in general, to see a table created from a view. However, there is an agrument that in this way they buy advantages of the subsequent querying from the table compare to "executing a view on the fly". I would rather not argue with them. But I am generally-curious here: could there be ANY situations when this approach is performance-rewarding? I understand that much depends on indexes, including those of the view's underlying tables', but, in general...???
more ▼

asked May 23, 2011 at 01:05 PM in Default

Tatyana gravatar image

Tatyana
413 15 15 18

All the answers are extremely helpful and giving different angles and considerations of the problem (and possible solutions). This discussion will definitely help me to (to begin with) make a peace with the current solution, as well as to look for ways to improve it, such as exploring an indexed view possibility. Thank you so much to all! ). I wish it would be possible to mark all of answers here as the answer.
May 24, 2011 at 05:58 AM Tatyana
(comments are locked)
10|1200 characters needed characters left

5 answers: sort newest

Just to add some more weight experiences to your list of answers. At the organization I work at I have inherited a number of systems that do just what you described in your question. I agree with @KenJ that having staging tables is a huge plus in many situations. Several of the daily tables that are "refreshed" in my organization are pretty well hammered during the day. Part of the process to refresh the data which sometimes is called by views performs various costly calculations on very large data sets. The same queries could be called by end users each time they wanted to access the data but based on some of the business logic from getting min, max, calculated values, max of calculated values etc, it is easier to have that data set already computed and the end users hit that data set. There have been numerous other cases where it has been better to create a few indexes and create a sproc for the users to use rather than views. I have encountered numerous situations where a user was calling a view, that was calling another view, that guess what - called another view. This leads to very sloppy code and expensive processes.

At the end of the day you just have to test and see if you can improve the process. A quote I love is "If you truly want to understand something... Try to change it!"

As with everything SQL, there is usually a time and place for everything, you just have to know when it is right.
more ▼

answered May 23, 2011 at 01:49 PM

Tim gravatar image

Tim
36.4k 38 41 139

Thank you, Trad. I'll keep testing. So, at least THIS is 100% bad practice - having a view that calls for another view? Would you think that rewriting a "view from view" code will always improve a performance?
May 24, 2011 at 05:42 AM Tatyana
I wouldn't say 100% bad practice but you can usually improve the performance. I am not sure if this use to be a standard or not but judging by the large quantity of these scenarios I have seen it had to be very common many years ago.
May 24, 2011 at 06:03 AM Tim
(comments are locked)
10|1200 characters needed characters left

There are a lot of good answers here, and I agree with everything said so far.

I will say it is worth carefully considering whether or not this makes sense. It definitely can under certain circumstances (I have used this technique myself on some occassions), but it can also be overused and use inappropriately. I would for instance consider if an indexed view is more appropriate or depending on how much the data changed if there is a way of doing an incremental update rather than a complete recreation.

One reason to do something like this that no one else has mentioned yet is for reporting purposes you may actually want the data to be fixed in time rather than accurate up to the second. I used to have a procedure that would would copy certain tables at midnight precisely so our analysts could examine the status as it was at midnight.
more ▼

answered May 23, 2011 at 04:09 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(comments are locked)
10|1200 characters needed characters left
I've used this technique myself, particularly when dealing with a view that contains a CTE. I even blogged about it too... http://thelonedba.wordpress.com/2011/05/10/t-sql-tuesday-018-ctes-theyre-not-always-good-for-you/
more ▼

answered May 23, 2011 at 02:09 PM

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 20 44

For the scenarios mentioned in the post (Subsequent use of the CTE), it could be a nice feature to have a MATERIALIZE hint for the CTE like it exists in oracle. So when the CTE is referenced for the first time, then it is materialized and the all the subsequent calls to it use the materialized keyword.
May 24, 2011 at 06:04 AM Pavel Pawlowski
That does sound handy, yes.
May 24, 2011 at 06:30 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

It is common to use [staging tables][1] during data loads, and they can help performance in some situations.

If the view, or its underlying tables are very complex or frequently updated during processing, then working off a current copy can improve concurrency for other processes in the database.

When you are doing complex or repetitive transformations on very large data sets, it can be faster to do them in staging tables inside the database rather than in an SSIS package. Especially if you would have to pull your large data set over the wire and try to work with it in memory.

Multi-step loads often make use of staging tables to perform logical units of work before pulling the final results together.

In addition to the raw performance improvements you can realize with staging tables, you can also see improved turnaround times in maintenance and development tasks, as your packages and processes can be reduced to a somewhat larger number of simpler steps, making them easier and more reliable to maintain.

[1]: http://www.google.com/search?q=sql+server+staging+tables
more ▼

answered May 23, 2011 at 01:32 PM

KenJ gravatar image

KenJ
20k 1 3 12

+1, another good reason :)
May 23, 2011 at 01:35 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

Yes, there are. If there weren't those situations, then indexed views wouldn't exist. And for the times at which indexed views won't cover the requirement - scheduled materialisation (such as the example you have found) is often used.

An example I have is where data from devices is associated with the location where the device was at the time - and this can be changed (thus the view). However, the indexed view allows it to be indexed by location, which is how the data is always queried. So yes, maintaining the view takes effort - but it is worth it.

I'm not sure 'daily' and 'executes for several hours' are two phrases that I would be keen to hear in connection with this topic, however. In that case it sounds like, maybe, the 'return on investment' in terms of CPU and I/O time isn't that great, if anything at all.
more ▼

answered May 23, 2011 at 01:31 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

"scheduled materialisation" I've never heard that term before, but I love it!
May 23, 2011 at 01:37 PM KenJ
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x84
x65
x3

asked: May 23, 2011 at 01:05 PM

Seen: 5173 times

Last Updated: May 23, 2011 at 01:05 PM