Performance in DS vs Oracle

LavanyaRamesh007 · Post by **LavanyaRamesh007** » Thu Feb 12, 2009 5:01 am

Hi,
In my project the source is Oracle and target is also oracle. For some of the transformations like concatenations, substring functions are written in Oracle( in SQL) rather than doing in DS transformer. My point is that doing the smae in DS will yield good performance since DS uses parallelism concept.. in SQL since we are not making the query in parallel mode i think when the job goes to production there will be a hiccup in performance.
Kindly let me know abt the performance of both?

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Feb 12, 2009 5:18 am

Who said Oracle does not use "Parallelism" ? It may be how you have configured it.

Doing in a single query may inturn avoid a transformer or modify stage and thus reducing number of process spawned.

Perform few sample tests in oracle and DS to see which is useful. Also check the plan in both.

kduke · Post by **kduke** » Thu Feb 12, 2009 11:04 am

There are a lot of factors to consider. How loaded down is the database? Are you trying to off load resources from the database? Most of the time if you are doing simple transforms like straight copies then doing everything in the native database is faster. The more complex the transforms the better a ETL should become. There maybe other considerations like at some point Oracle may not be the target then DataStage is a better solution.

velagapudi_k · Post by **velagapudi_k** » Mon Feb 16, 2009 10:54 am

Sainath.Srinivasan wrote:Who said Oracle does not use "Parallelism" ? It may be how you have configured it.

Doing in a single query may inturn avoid a transformer or modify stage and thus reducing number of process spawned.

Perform few sample tests in oracle and DS to see which is useful. Also check the plan in both.

Sainath this is kind of interesting. How to check the plan in datastage?
Can you please let me know briefly or direct me to some documentation.
Appreciate your help.

mk_ds09 · Post by **mk_ds09** » Mon Feb 16, 2009 11:05 am

Yes..as it said it really depends on the various factors..

However doing the changes in quries is better in one of the aspect as it will avoid more i/p operations.
It will give the DS only the formated data.
Otherwise it will just read and give all the data to DS where DS needs to perform the changes.

In most of the cases, where you say performance is to get rid of the transformations/derivations in the early stage so that the later part of processing will not have that overhead !

Hope this makes some sense ! :D

richdhan · Post by **richdhan** » Tue Feb 17, 2009 12:41 am

Hi,

velagapudi_k wrote:How to check the plan in datastage?

The plan in Datastage is nothing but the job score.

Set $APT_DUMP_SCORE=1 and check the job score in the director. There will be 2 scores. One from the license operator. The next one is the actual job score.

The job score gives information on the number of processes, framework inserted operators, the partition and collection between operators, operator combination etc.

HTH
--Rich

velagapudi_k · Post by **velagapudi_k** » Tue Feb 17, 2009 8:39 am

Thanks Rich.