Why does Qlik performs aggregations slower than SQL server?

Fri, 17 Feb 2017 09:07:57 GMT

Hello all!

I need to make simple aggregation on huge fact table 300M rows during ETL as fast as possible:

AggregatedFact:

LOAD

StoreId, GoodsId, RegionID,

sum(SellingSum) as SellingSum,

sum(PurchaseSum) as PurchaseSum

RESIDENT FactQVD

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!

Re: Why does Qlik performs aggregations slower than SQL server?

Fri, 17 Feb 2017 10:36:31 GMT

bill.markham‌ answered here: Re: How to debug Qlik Sense script huge memory consumption?

topic Why does Qlik performs aggregations slower than SQL server? in Connectivity & Data Prep

Why does Qlik performs aggregations slower than SQL server?

Re: Why does Qlik performs aggregations slower than SQL server?