How to increase the Speed of Data read from Oracle Database

ashishpascal · Post by **ashishpascal** » Fri Mar 28, 2008 5:07 am

Hi all,

I have developed a server Job in which i am reading Data from a Oracle Database and then through Transformer puts the DAta Into SAPBW LOAD PACK.
Currently when i increased the Array Size to 2000 the reading speed is 700 records per second. IS there any way i can increase this speed to 7000 records per minute?

Thanks
Ashish

ray.wurlod · Post by **ray.wurlod** » Fri Mar 28, 2008 5:11 am

Assuredly.

ashishpascal · Post by **ashishpascal** » Fri Mar 28, 2008 5:18 am

Currently with 700 records per second , the job is taking 2.5 hrs to complete.
could you tell me as to how could i increase the reading speed to 7000 records per second.?

chulett · Post by **chulett** » Fri Mar 28, 2008 6:49 am

Are you sure your LOAD isn't the bottleneck? Replace it with a Sequential stage to check, then tune your source query to be as optimal as possible. Oracle, not DataStage is where you need to solve this 'problem'.

ArndW · Post by **ArndW** » Fri Mar 28, 2008 6:58 am

ashishpascal - there are too many ways to slow down and speed up jobs to be able to answer your question. First step - know thyself. Is your read or write the bottleneck?

ashishpascal · Post by **ashishpascal** » Fri Mar 28, 2008 6:59 am

when i tried to read data from Oracle Database and write it to Sequential file , the job took just 10 minutes and when instead of Sequential File i am using SAPBW LOAD PACK (to send the data to SAP BW server) , its taking more than 2.5 Hrs to complete the Job.
This SAPBW LOAD PACK is the Bottle Neck.
Can you just suggest me some way so that i can reduce this 2.5 Hrs to less than an Hour , as i need to bring in this SAP Load Pack into the Job Design and the very function of Load pack is to Reduce the Transfer time.

Thanks
Ashish

ray.wurlod · Post by **ray.wurlod** » Fri Mar 28, 2008 4:21 pm

ashishpascal wrote: the very function of Load pack is to Reduce the Transfer time.

Compared to what?

SAP is going to do an immense amount of validation of data being sent into it. DataStage sends data as fast as SAP is prepared to accept them.