Using system Date variables making JOB slow

[manish1005]

Hi,

My requirement is to write system timestamp in one of columns of target table. Even if the job runs for two hours, it is okay to populate table with a single timestamp value instead of extracting timestamp from system for each row.

In one of the transformations I am setting DSJobStartTimestamp value to a stage variable and using that for the required column. I noticed now that job is running too slow on my dev machine with just 50rows per second. When I substitute DSJobStartTimestamp with some timestamp value like "2008-10-31 21:59:59" job process at the rate of 1300 rows per second!

I have tried @DATE and datetime() as well but performance is slow when I use them.

Why does using DSJobStartTimestamp makes the job slow, its just a variable - and DS may not even need to probe system for every row for this since it is fixed within a job!


One way I can think of is to write a simple job to extract date/timestamp from system pass it to @User variable and use that in my current job. I really dont want to create a separate job for this thing. Is there any other possible way?
Please suggest

Btw job is quite simple:
source oracle stage--> TRN with 3 stage variables and minor computation-->Target oracle stage

[chulett]

It will only be 'fixed within a job' if you use the Sytem Variable in the Initial Value of the stage variable, otherwise it will be evaluated for each record. That and leave the derivation in the Transformer empty.

[manish1005]

Did that and it worked. Thanks.

[ray.wurlod]

DSJobStartTimestamp is NOT a variable - it's a macro that invokes DSGetJobInfo(DSJ.ME, DSJ.JOBSTARTTIMESTAMP)

Evaluating this for every row could certainly be expected to increase overall execution time.