DSXchange

Need guidelines for Usage of Routines:
When to use routines?
What are the advantages and disadvantages of using routines?

(this sounds suspiciously like an interview question)

Use a routine when you can't solve an operation without using one.

The advantages and disadvantages of using routines are the same as with any programming language. Routines add execution time since they must be called (i.e. pushed onto and then popped off the executation stack) but save maintenance and development time when they can be reused.

Hi,

Calling a routine creates a dynamic call while putting your logic in a transform creates a static call.

This as 2 implications:
1.) When you change a routine you do not have to do nothing, just compile it and from now on all jobs that use this routine will use the new version. While with transforms you have to compile all the jobs that uses it.

2.) The routine call has a performance cost. Calling a transform is much faster.

So in case of large volumes, use our colleagues' advise and avoid using routines unless you must.


Amos

Amos.Rosmarin wrote:2.) The routine call has a performance cost. Calling a transform is much faster.

I'm not so sure this is true. It comes up every now and then and I seem to recall Ray begging to differ on the subject.

Now, obviously comparing a limited-to-one-line transform to a routine which could be very complex is a little on the apples and oranges side. I believe that the overhead of calling the routine is spent in the first call to it, not each and every call. I'd have to search back through the forums for other posts on this subject and see if I can find what I am thinking of...

Usage of routines comes to play when customized transformations or functionality is required by the business or the requirement of the ETL.

Craig,

I did some testing a while back on the performance differences and the PCL does have a significant overhead when the routine itself only does a couple of actions. This is mainly internal overhead for pushing and popping off the stack.

I just did a quickie test of a 1-liner routine call and doing it in-line took 109 milliseconds and doing it via a routine took 1625. This was a trivial A*B*C operation executed inside 3 loops of 100 (i.e. 1,000,000 times). You can see that the overhead of ~1.5 seconds isn't very much but it remains much more than by leaving it out.

Good to know, Arnd. An additional 1.5 seconds over the course of a million rows isn't a deal breaker to me, but the overhead is something to be aware of.

Just having this 'deja vu all over again' feeling, as I know this conversation has played out more than once in the past.

Found the post I was thinking of. Seems my memory was a little... off... as the clarification Ray made was specific to a comment *I* made in the post about 'context switching' and not generally about the performance of a routine versus a transform.

Anywho, worth linking here as it's another patented 'Full Wurlod' type post.