Hello i have the following script that i am collecting data. The number of the records are huge over 20.000.000 records per month in the level of item. With the following script i collect data from sales joining some information about stores and items and i store them into a QVD.
SALES:
LOAD ITEM_ID,
STORE_ID,
SALES_DATE,
SALES_WEEK,
SALES_YEAR,
SALES_MONTH,
SALES_QTY,
SALES_AMT,
SALES_AMT_FPA,
PRICE_LIST
FROM \\99.99.999.99\qvdstorage\SALES_2016_01.QVD (qvd);
LEFT JOIN
LOAD STORE_ID,
FORMAT,
STORE_DESCR,
STORE_REGION,
SQUARE_MEASURES,
FROM C:\Stores\stores.xls (biff, embedded labels, table is [Sheet 1$]);
LEFT JOIN
LOAD
ITEM_ID,
[SECTEUR DESCRIPTION],
[RAYON DESCRIPTION],
[DESCRIPTION FAMILY],
[DESCRIPTION SUBFAMILY],
[DECRIPTION UB],
[DESCRIPTION SUBUB]
FROM C:\QVDS\GLOBAL\ITEMS_GLOBAL.QVD (qvd);
STORE SALES INTO C:\QVDS\GLOBAL\SALES_GLOBAL_2016_01.QVD;
DROP TABLE SALES;
After that i'm doing the following in order to have the summaries in the level of Format, Store, Year,Date, e.t.c
LOAD SALES_YEAR,
SALES_MONTH,
SALES_DATE,
SALES_WEEK,
FORMAT,
STORE_ID,
(SUM(SALES_AMT_FPA)-SUM(SALES_QTY*PRICE_LIST))/SUM(SALES_AMT_FPA) AS CESSION_MARGIN,
SUM(SALES_QTY) AS QTY,
SUM(SALES_QTY*PRICE_LIST) AS COGS,
SUM(SALES_AMT) AS GROSS_SALES,
SUM(SALES_AMT_FPA) AS NET_SALES,
[SECTEUR DESCRIPTION],
[RAYON DESCRIPTION]
FROM C:\QVDS\GLOBAL\SALES_GLOBAL_2016_01.QVD (qvd)
WHERE FORMAT = 'HYPFL' OR FORMAT = 'HYPMI' OR FORMAT = 'SUPER' OR FORMAT = 'PROXI' OR FORMAT = 'CASHC'
GROUP BY SALES_YEAR,SALES_MONTH,SALES_WEEK,SALES_DATE,FORMAT,STORE_ID,[SECTEUR DESCRIPTION],[RAYON DESCRIPTION];
All this procedure it takes a lot of time. How can i rewrite it in order to decrease the time of reload ?
Thank you in advance.
