SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

"Create as View" in DI Studio

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 89
Accepted Solution

"Create as View" in DI Studio

Can someone please inform me of the consequences of enabling/disabling the "Create as View" option on transformation output ports in DI Studio? What kind of situations should I do it in? If you know of a good resource where i can read about this, please let me know. Thank you. Smiley Happy


Accepted Solutions
Solution
‎10-04-2012 10:20 AM
Respected Advisor
Posts: 3,899

Re: "Create as View" in DI Studio

My rule of tumb is: Whenever a data object is used more than once I create it as table. A view is kind of encapsulated SQL code which gets executed whenever you "touch it" - so the same query is run several times if you use a view several time as source.

On the other hand: A view is done in memory so you have less disk I/O which is beneficial if you need the view only once. But then: This is only true if there is enough memory. If not then "paging" starts meaning that memory gets written to disk to make space for the data needed right now - and this back and forth can end up in much more disk I/O then when directly creating a table. So sometimes: You just need to test and see how it runs faster.

While developing: I normally create tables as then I can easily run node by node. Because as Linus wrote: A SQL view gets only executed when used in the code so only in this node will you get the error (but actually the error code is in an upstream node). That's quite annoying for debugging.

So I'm normally only changing to views in the very end of unit testing when trying to make it run faster. If performances is not of importance then I often don't bother to switch over to views as using tables gives you also later on in the log dircetly the info how many rows got created in the place where it happens. So it's also in a operational environment just a bit easier to find an issue by reading the log.

View solution in original post


All Replies
Super User
Posts: 5,260

Re: "Create as View" in DI Studio

In general, chose what is best for the application.

If the result set is called from more than one other transformation, set is a table.

If you have some syntax problem and using a view, the error will occur at the en of the flow, and can somtimes be hard trouble shoot. In these cases you can temporarily change table.

Data never sleeps
Solution
‎10-04-2012 10:20 AM
Respected Advisor
Posts: 3,899

Re: "Create as View" in DI Studio

My rule of tumb is: Whenever a data object is used more than once I create it as table. A view is kind of encapsulated SQL code which gets executed whenever you "touch it" - so the same query is run several times if you use a view several time as source.

On the other hand: A view is done in memory so you have less disk I/O which is beneficial if you need the view only once. But then: This is only true if there is enough memory. If not then "paging" starts meaning that memory gets written to disk to make space for the data needed right now - and this back and forth can end up in much more disk I/O then when directly creating a table. So sometimes: You just need to test and see how it runs faster.

While developing: I normally create tables as then I can easily run node by node. Because as Linus wrote: A SQL view gets only executed when used in the code so only in this node will you get the error (but actually the error code is in an upstream node). That's quite annoying for debugging.

So I'm normally only changing to views in the very end of unit testing when trying to make it run faster. If performances is not of importance then I often don't bother to switch over to views as using tables gives you also later on in the log dircetly the info how many rows got created in the place where it happens. So it's also in a operational environment just a bit easier to find an issue by reading the log.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 670 views
  • 3 likes
  • 3 in conversation