1 Reply Latest reply: Feb 17, 2017 5:36 AM by Maxim Bregeda RSS

    Why does Qlik performs aggregations slower than SQL server?

    Maxim Bregeda

      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!