Guideline for Routine Usage

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
Shruti
Participant
Posts: 1
Joined: Wed May 10, 2006 9:20 pm
Location: Pune

Guideline for Routine Usage

Post by Shruti »

Need guidelines for Usage of Routines:
When to use routines?
What are the advantages and disadvantages of using routines?
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

(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.
Amos.Rosmarin
Premium Member
Premium Member
Posts: 385
Joined: Tue Oct 07, 2003 4:55 am

Post by Amos.Rosmarin »

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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...
-craig

"You can never have too many knives" -- Logan Nine Fingers
DSguru2B
Charter Member
Charter Member
Posts: 6854
Joined: Wed Feb 09, 2005 3:44 pm
Location: Houston, TX

Post by DSguru2B »

Usage of routines comes to play when customized transformations or functionality is required by the business or the requirement of the ETL.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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. :?
Last edited by chulett on Sun May 14, 2006 6:31 am, edited 2 times in total.
-craig

"You can never have too many knives" -- Logan Nine Fingers
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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. :lol:
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply