Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi Gou Geo,
You can try apply map instead of Left join. I haven't tried but you can give a try. See
Your final LOAD is from a qvd and the WHERE and GROUP BY will make it a slow unoptimised load.
As this qvd was created and stored earlier in you script you could try loading it from resident instead and get rid of the STORE and move the DROP TABLE to the end.