High Data Volumn ETL Performance

shiva

• November 10, 2009 00:00

• Answered

Follow

Hi

I am trying to load data from MySql db to MySql db (1000000 records)

Clover ETL taking more than 10 minutes to transfer data with in same Database,


Q: - how can possible to increase performance of ETL? Could u give any of ur suggestions…..




Thanks& Regards
Shiva

• 

  avackova

  • November 11, 2009 08:41

  0

  Comment actions Permalink

  Hello,
  the best practice to load data from one table to another in the same database is to do it inside the database. If you want to do it with CloverETL, you can use DBExecute component with query like this: insert into my_table select * from another_table. Pulling data out from the database and putting them in has to be slower while the data has to be parsed during the reading and formatted when writing.
  Here are some tips for speed increasing of DBInput/OutputTable component:
  

  • ensure you use our built-in connection with proper jdbcSpecific
  
  
  • increase the fetchSize parameter in DBInputTable
  
  
  • enable batchMode and increase batchSize=10000 and commitSize=1000000 in DBOutputTable
  
  
  • use bulk loader instead of DBOutputTable :wink:
• 

  ciscochi

  • November 12, 2009 22:03

  0

  Comment actions Permalink

  Shiva,
  You also have to consider the slowest point in your operation: network. You are pulling data out of your database which has to travel through the network to where your ETL process is running (local client?), then it has to go all the way back to the database.
  
  Chi

Please sign in to leave a comment.