Slow Performance ETL
951538Jul 25 2012 — edited Jul 25 2012Hello, my name is Reynaldo Abimael Aceves Chamosa ( iAbimac )
I need to make a system that shows the network performance every 30 minutes, I use sqlldr to load information in around 15 tables with the same PK(DATE,HOUR,MINUTE,SYS_NAME,CELL_NAME,SITE_NUMBER,CELL_ID_X,SECTOR) each table is loaded with 20,000 rows, I run a SELECT statement in order to delete and then insert the new records into the final table "UMTS_SECTOR_30", the SELECT join the 15 tables with the same PK, but the problem is that this solution is slow and I need enhancing the performance of this process, the final table is partitioned by range by date every day, when the delete-insert finishes I truncate the other 15 tables, What do you recomend?, What could be the best performance solution?, I have tried using MERGE(update-insert) upsert, but still slow.
FIRST DELETE
select t1.DATE,t1.HOUR,t1.MINUTE,t1.SYS_NAME,t1.CELL_NAME,t1.SITE_NUMBER,t1.CELL_ID_X,t1.SECTOR
t1.field1 + t1.field2 "counter1"
t2.field1 - t2.field2 "counter2"
t3.field1 * t3.field2 "counter3"
t4.field1 - t4.field2 "counter4"
t5.field1 * t5.field2 "counter5"
t6.field1 / t6.field2 "counte6"
t7.field1 + t7.field2 "counte7"
from t1,t2,t3,t4,5,t6,t7
where
t1.DATE = t2.DATE AND
t1.HOUR = t2.HOUR AND
t1.MINUTE = t2.MINUTE AND
t1.SYS_NAME = t2.SYS_NAME AND
t1.CELL_NAME = t2.CELL_NAME AND
t1.SITE_NUMBER = t2.SITE_NUMBER AND
t1.CELL_ID_X = t2.CELL_ID_X AND
t1.SECTOR = t2.SECTOR AND
--
t1.DATE = t3.DATE AND
t1.HOUR = t3.HOUR AND
t1.MINUTE = t3.MINUTE AND
t1.SYS_NAME = t3.SYS_NAME AND
t1.CELL_NAME = t3.CELL_NAME AND
t1.SITE_NUMBER = t3.SITE_NUMBER AND
t1.CELL_ID_X = t3.CELL_ID_X AND
t1.SECTOR = t3.SECTOR AND
...
This query merge the 15 other tables in only one.
For instance:
t1 20120512,15,00,TOL,123_MEX-456_123,MEX-456,,456,6,value1
t2 20120512,15,00,TOL,123_MEX-456_123,MEX-456,,456,6,value2
t3 20120512,15,00,TOL,123_MEX-456_123,MEX-456,,456,6,value3
t4 20120512,15,00,TOL,123_MEX-456_123,MEX-456,,456,6,value4
final_table
20120512,15,00,TOL,123_MEX-456_123,MEX-456,,456,6,value1,value2,value3,value4