Extract data efficiently

loe_ram13 · Post by **loe_ram13** » Fri Apr 13, 2007 3:10 am

I have to extract data from a table that has summarized historical records of past 1 year.
Then I merge and aggregate the data with current month's data.
Then i truncate the table and load the merged data again.
Is there a better way than doing this,as extracting the data takes more than 6 hours.
Pls help for performance tuning.........
Thanx

chulett · Post by **chulett** » Fri Apr 13, 2007 5:35 am

Welcome. Pretty big work order on a first post - tune a job for you. And made more difficult because we don't know any specifics of your job design.

How about this? Identify your bottleneck or bottlenecks. Attack them one by one. If you mentioned that 'extracting takes 6 hours' you think it could take less time for the volume of records involved, yes?

Describe your 'extraction' segment - what stages are used? What databases are involved? What is the volume of data? What kind of transformations are you doing? Are you going directly from db to db and suffering from 'back pressure'? Search for those two words to find a description of and resolution to that phenomena, if it is applicable.

Let's start with that.

loe_ram13 · Post by **loe_ram13** » Fri Apr 13, 2007 5:58 am

1.There is a base table from which i extract data for the previous entire month and store it in a .dat file.
2.Then i take the entire data from the target table (which do not have prevoius month data) and again store it in a .dat file
3.Then i merge and aggregate these file.
4.Then i truncate the target table.
5.Then i load the merged .dat file to the target table.
Here,step 2 takes more than 6 hours.
Is there any alternative than truncating entire data and loading it again.

chulett · Post by **chulett** » Fri Apr 13, 2007 6:48 am

chulett wrote:Describe your 'extraction' segment - what stages are used? What databases are involved? What is the volume of data? What kind of transformations are you doing?

ray.wurlod · Post by **ray.wurlod** » Fri Apr 13, 2007 3:17 pm

Craig's questions are pertinent; without knowing what stage types you are using and data volumes you are dealing with, for example, it would be imprudent to provide advice. What is the basis for the merge? Are the source and target tables in the same database instance?