I need to make simple aggregation on huge fact table 300M rows during ETL as fast as possible:
StoreId, GoodsId, RegionID,
sum(SellingSum) as SellingSum,
sum(PurchaseSum) as PurchaseSum
GROUP BY StoreId, GoodsId, RegionID;
Then run on my QS server 3.1 SR5 (physical machine, 192 GB RAM; 12 cores), the execution time: 1h 30 min + 10 minutes to pre-load FactQVD from disk.
Then run on my SQL Server 2016 (virtual machine, 32 Gb RAM; 4 cores, lower frequency than QS server has): build 3 indexes (1 hour) then run simple GROUP BY query (50 min). Total executioin time: 1h 50min.
Why is Qlik Sense just 10% faster on this hardware?
May be I missed some performance configurations after the Qlik install?
Thank you in advance!