Solved: Re: "Create as View" in DI Studio

EinarRoed · Posted 10-04-2012 06:36 AM

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.

Patrick · Posted 10-04-2012 10:20 AM

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

LinusH · Posted 10-04-2012 08:35 AM

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

Patrick · Posted 10-04-2012 10:20 AM

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.