Home » Developer & Programmer » Warehouse Builder » problem in ETL with large data
problem in ETL with large data [message #153445] Mon, 02 January 2006 07:51 Go to next message
samar@infoprise
Messages: 4
Registered: January 2006
Location: lebanon
Junior Member

i am working with ETL, but i am facing a problem which is when have large amount of data (1 million records) the ETL takes about 4 to 5 hours to be finished which is a long time. so is there any way to optimize this time.

Any comment may be helpfull for me.
Thanks.
Re: problem in ETL with large data [message #155921 is a reply to message #153445] Thu, 19 January 2006 12:22 Go to previous messageGo to next message
d.fowler
Messages: 21
Registered: January 2006
Location: Missouri
Junior Member


Is this a mapping issue? Did a package get created from the map process. My have to run trace on generated plsql package.

How was output process step to final table defined. Record at a time format? error check at record level slow.....
Re: problem in ETL with large data [message #155975 is a reply to message #155921] Fri, 20 January 2006 01:43 Go to previous messageGo to next message
Maaher
Messages: 7053
Registered: December 2001
Senior Member
Dave is right: run a trace on the package.

You have to identify what part is slowing the process down. You might want to use a test source that is somewhat smaller. One option might be to use a log procedure (in an autonomous transaction) to identify at what timestamp you arrive at a given point in your process.

MHE
Re: problem in ETL with large data [message #157895 is a reply to message #153445] Tue, 07 February 2006 01:33 Go to previous messageGo to next message
amits76
Messages: 5
Registered: February 2006
Junior Member
samar,
There are quite a few points you might have to look at.
1) Look whether all columns on which you have made a join are indexed or not.
2) Analyse all the tables,if possible analyse the schema.
3) if you are using filters and your join condition has where clause ,shift those to filters...
4) Avoid using union operators.
5) Sort your table data.
6) Carry forward onlt those columns which you need from any source table.
7) Run mapping in Set based only.
Cool Give commit freq as 1000.
9) Break the big maping into small mapping.
.
Hope this helps.
Re: problem in ETL with large data [message #157906 is a reply to message #157895] Tue, 07 February 2006 02:24 Go to previous messageGo to next message
samarsheet
Messages: 7
Registered: November 2005
Junior Member
Thank u for your help

Re: problem in ETL with large data [message #159126 is a reply to message #153445] Thu, 16 February 2006 05:34 Go to previous message
jayadesh
Messages: 10
Registered: February 2006
Location: INDIA
Junior Member
HELLO GUYS,
ONCE U LOAD DATA IN TARGET ,WHICH IS HAVING INDEXED COLUMNS OBVIOUSLY IT WILL TAKE TIME.
SO TRY TO DROP THE INDEX IN PRE-SESSION AND CREATE IT IN POST-SESSION SQL.
IT MAY HELP UPTO ANY EXTEND.
THANKS
Previous Topic: WB 10gR1 and Asynchronous CDC
Next Topic: Looping in OWB 10g
Goto Forum:
  


Current Time: Wed Sep 17 03:03:31 CDT 2014

Total time taken to generate the page: 0.18686 seconds