Boost ETL Performance: Practical Tips to Optimize Inceptor Transaction Tables

In the previous article "ETL Tuning Sharing (Part 1)" we introduced the importance of ETL performance for data warehouse construction; this follow‑up presents additional practical recommendations.

Avoid using unnecessary transaction tables.

Minimize the window time of transactional operations.

Start analysis with the cases that most affect overall performance.

Iterate steps repeatedly until the optimal result is reached.

Avoid Unnecessary Transaction Tables

Because ORC transaction tables are slower to read and write, use regular ORC tables for data that does not require transactional guarantees. When needed, manually trigger a major compaction to reduce the number of delta files and improve query speed.

Case Study

CREATE TEMPORARY TABLE test_transaction_tmp(
  id string, name string)
CLUSTERED BY (id) INTO 3 BUCKETS
STORED AS orc_transaction;
!set plsqlUseSlash true
SET transaction.type=inceptor;
SET plsql.catch.hive.exception=true;
begin
  begin transaction;
  insert into test_transaction_tmp
    select * from default.test_transaction_use;
  dbms_output.put_line("step1 : "||sql%rowcount);
  delete from default.test_transaction_target where 1=1;
  dbms_output.put_line("step2 : "||sql%rowcount);
  insert into default.test_transaction_target
    select * from test_transaction_tmp;
  dbms_output.put_line("step3 : "||sql%rowcount);
  commit;
exception
  when others then
    rollback;
    dbms_output.put_line("step4 : "||sql%rowcount);
    dbms_output.put_line('Exception caught, error code = ' || sqlcode());
    dbms_output.put_line('error message = ' || sqlerrm());
end;
/

The temporary table was created as a transaction table, but it only performed insert and select operations, so a regular ORC table would be faster and avoid transaction overhead.

Reduce Transaction Window Time

Inceptor implements transactions with table‑level locks. In high‑concurrency scenarios, shortening the time the lock is held is crucial. The lock is acquired at the delete statement and released after commit, so optimizing this window reduces serialization.

Case Study

Same SQL as above demonstrates that the lock spans from the delete statement to the commit, causing potential bottlenecks under concurrent loads.

Start Analysis with the Most Impactful Cases

After addressing the first five steps, focus on the remaining slow jobs. Use EXPLAIN to verify plan reasonableness, check predicate push‑down, and evaluate join order. If the optimizer does not handle a case, manually adjust the SQL or enable CBO. For large‑table joins, consider disabling MapJoin to improve parallelism.

Iterate Until Optimal Performance

Continuously refine the previous steps, monitor performance via Inceptor’s 4040 UI or jstack, and resolve identified bottlenecks until the overall ETL meets the required efficiency.

Conclusion

Combining the insights from both parts of this series provides a practical checklist for improving ETL efficiency in data‑warehouse projects.