Extraction Job which one is faster?

williamswe · Post by **williamswe** » Fri Jun 23, 2006 3:15 am

Hi all,

I am extracting aggregated data from the warehouse using oracle sql ( invovled joining dimension and fact tables , summing up some measures, some translation of data column and sorting )
Now i need to build the DS job for it.

-should i get better performance if I break the logic down into a series of stages ( like ext, join, xfm, aggre, sort...etc )?
-Or let the Oracle handle all the processing? ( I am pulling data from one source only )

Thanks and Regards,
william

ArndW · Post by **ArndW** » Fri Jun 23, 2006 3:21 am

William,

there is no single good answer to that. If your SQL logic includes a big reduction in the amount of data then often it is faster to do it in the database and save I/O. Sometimes the DB machine is more heavily loaded than the DS or ETL machine, so processing can be faster inside DataStage.

kumar_s · Post by **kumar_s** » Fri Jun 23, 2006 5:46 am

It also depeds on where you database resides. If it is costly have carry huge number of rows from database server to DS server, it might be effecient to do the necessay aggregation steps from DS by either stroed procedure or using your SQL logic, and carry the necessay records for transformation to DS server.

DSguru2B · Post by **DSguru2B** » Fri Jun 23, 2006 7:12 am

Also, you need to always keep in mind, what time, or what part of day or night is your process going to run when it goes live. If during that time, not much database activity is taking place, then i would suggest doing aggregations sorting and joins at the database level.Transformations can be done in datastage.
My 2 centy.