Using system Date variables making JOB slow
Posted: Mon Jul 07, 2008 12:02 pm
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
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