topic Re: Performance issues: ETL with Index rebuild vs Drop/Create in Talend Studio

Performance issues: ETL with Index rebuild vs Drop/Create

Anonymous — Thu, 17 Mar 2016 22:06:01 GMT

Hello,
I have seen performance difference of about 10 times slower loads when I do a rebuild on indexes (disable before load, load, enable indexes after load) compared to dropping indexes -> load -> create indexes. This is weird as we would like to rebuild table indexes calling a package that has index metadata. This way index metadata is safely retained always.
ETL logic is pretty simple and should not effect the load and I have confirmed that by creating a duplicate copy of the same job with ETL loading data into a buffer out and then triggers bufferin -> target_table_load. Here again, ETl finishes loading into bufferout in 50secs, but reading from bufferin and loading to target goes extremely slow (10mins).
attached is a screenshot of entire load with 3 scenarios for comparision
1) drop & create table (no indexes)
2) rebuild index (disable,enable)
3) Create/drop index with truncate data

Re: Performance issues: ETL with Index rebuild vs Drop/Create

Anonymous — Fri, 18 Mar 2016 00:15:31 GMT

This is not a question about Talend, more of a question about whatever database it is you use.

Re: Performance issues: ETL with Index rebuild vs Drop/Create

Anonymous — Thu, 02 Jun 2016 18:40:07 GMT

Using cursor (read 1000 rows) per set helped, as well as dropping index and creating after load reduced time greatly. I am guessing splitting data flow into parallel flows would help even more (tpartition) which I don't have access to even though we have enterprise licensed version of Taled for DI.