Incremental load taking more than 20 minutes for Fact. Any way to reduce it?
Hi Guys,
I just wonder, what I have done is right or wrong for Incremental load?
I have Fact table with 600 Million records. 1st I am loading data into .QVD file and then performing incremental load every day for 1 million records.
Sales:
//Existing records
LOAD
* FROM C:\SALES_DATA.QVD(QVD); //(600 Million Records)
CONCATENATE (Sales)
//New records receiving every day
SQL
SELECT * FROM DBO.Sales; //(1 Million Records ever day)
STORE Sales INTO C:\SALES_DATA.QVD;
DROP TABLE Sales;
Note: There is no duplicate records as we have handled duplicates in my DBO.Sales Fact table. and * means we are fetching only required columns.
If I do full load every day (loading complete data from Sales table from DB to .QVD) It is taking 35 minutes and If I go with above approach it is taking 20 minutes.
Now I am looking for something to just append 1 Million new Records ever day to existing SALES_DATA.QVD without loading existing data 600 Million Records into memory.
Is this possible ? Yes then how and no then any other alternative to reduce load time?