topic Re: Slow Qlik document in QlikView

Slow Qlik document

pauldamen — Thu, 28 Aug 2014 08:41:44 GMT

All,

I have a Qlik document which loads from 3 QVD's (around 500.000 lines in total).

And I have 5 tabs with some objects, my problem is, it is extremely slow. Every time I make a selection it eats up all my CPU and takes forever to change.

What did I do wrong that makes the document that slow?

Regards,

Paul

Re: Slow Qlik document

datanibbler — Thu, 28 Aug 2014 08:53:50 GMT

Hi Paul,

I'm downloading your document, but our internet_connection is a bit sluggish at times, it might take a while 😉

Just as a first hint - when the app loads acceptably fast, but every selection causes it to work for a while, it means that your object/ chart is too complex. That might have to do with calculated dimensions or a nr. of other issues.

=> If you have calculations in your chart, try to move as many of them as possible from the GUI into the script (there they are executed only once, not with every new selection).

Sorry I cannot give you a more precise hint right now, I'd have to look at your .qvw.

HTH

Best regards,

DataNibbler

Re: Slow Qlik document

rustyfishbones — Thu, 28 Aug 2014 08:55:08 GMT

Hi Paul,

It's the same when I try to Open the file, really slow.

Can you reduce the Data by only loading FIRST 1000 rows for each QVD and repost the reduced App.

Or can you post an image of the Table Viewer

Regards

Alan

Re: Re: Slow Qlik document

pauldamen — Thu, 28 Aug 2014 09:03:47 GMT

See first 1000 rows load for each QVD attached

Re: Slow Qlik document

ashfaq_haseeb — Thu, 28 Aug 2014 09:12:03 GMT

Hi,

I have seen your both files.

Its because of heavy calculation you used in fronted.

I suggest to take all calculation (As much as you can) to your script.

Regards

ASHFAQ

Re: Slow Qlik document

pauldamen — Thu, 28 Aug 2014 09:14:35 GMT

My calculations contain a lot of sums, but that doesn't work in the script?

How can I solve that?

Re: Slow Qlik document

tresB — Thu, 28 Aug 2014 09:16:47 GMT

Your front end expressions are too complex. Try to optimize it. One hint:

Instead of : =Sum(A)-Sum(B)-Sum(C)- .... try

=Sum(A-B-C)

Re: Slow Qlik document

rustyfishbones — Thu, 28 Aug 2014 09:17:26 GMT

Hi Paul,

You Customer as Dimension in some of the Charts and there are 346,080 this could be causing the speed issues.

Also the SubsetRatio of Main Dim Table is 3 % this won't help you either, you have rows in your Dimension Table than you do in your fact table, so you have lots of Customers that don't have facts you need to remove these in your script

Can you share your qvd's and we can take a look at creating a better Data Model perhaps

Re: Slow Qlik document

rustyfishbones — Thu, 28 Aug 2014 09:18:59 GMT

Hi Paul,

Change your script in your main tab to below and reload the document.

Let me know if the speed increases

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0;€ -#.##0';

SET TimeFormat='h:mm:ss';

SET DateFormat='D-M-YYYY';

SET TimestampFormat='D-M-YYYY h:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='ma;di;wo;do;vr;za;zo';

Main_Exp:

LOAD Date,

[Rateplan Sim Only Grouping],

CUSTCODE,

Revenue,

Discount,

SaldoTotal,

GrossAds,

Renewals,

CreditAmount,

IAS,

VOLUME_COGS,

[IAS Name],

Rate,

COGS,

Code,

RateMI,

#Customers,

UnbilledAmount

FROM

Main_Exp.qvd

(qvd);

Main_Dim:

LOAD CUSTCODE,

MainAccount,

Customer,

HOLDING_KEY_NEW,

Channels,

[Ds Ids],

CUSTOMER_ID,

RatePlanCode,

RatePlanName,

Segment

FROM

Main_Dim.qvd

(qvd)

WHERE EXISTS (CUSTCODE);

Temp_1:

LOAD Type,

Date,

ExtraCustomerAmount

FROM

Temp.qvd

(qvd);

Re: Slow Qlik document

pauldamen — Thu, 28 Aug 2014 09:20:22 GMT

No it doesn't help

Re: Re: Slow Qlik document

pauldamen — Thu, 28 Aug 2014 09:22:48 GMT

That would be great, please find my QVD's attached. I also attached the QVW that creates the QVD's from the sources

Re: Slow Qlik document

rustyfishbones — Thu, 28 Aug 2014 09:38:58 GMT

Hi Paul,

Can you Comment out your Calendar Table and reload and let me know if that speeds up your Object calculations

Regards

Alan

Re: Slow Qlik document

pauldamen — Thu, 28 Aug 2014 09:53:23 GMT

It looked to be working, but as soon as I add a year field to the load it goes bananas again.

Should I create the year, month etc, fields also before the QVD creation?

Re: Slow Qlik document

rustyfishbones — Thu, 28 Aug 2014 10:00:09 GMT

Hi Paul,

I found a Date in the Data 1-0-2013, maybe that is causing an issue.

Also I recommend you don't join tables with Date as the link.

For now you should create the calendar fields from the Date in Main_Exp and get going again.

But try removing 1-0-2013 from the Main_Exp Date field, or fix it at source

Re: Slow Qlik document

rustyfishbones — Thu, 28 Aug 2014 10:02:54 GMT

Yes I would try adding at the Create QVD stage

Re: Slow Qlik document

pauldamen — Thu, 28 Aug 2014 11:07:07 GMT

Added the dates before the QVD creation, unfortunately it still remains slow

Re: Slow Qlik document

rustyfishbones — Thu, 28 Aug 2014 12:14:31 GMT

Hi Paul,

It appears the guys maybe correct.

you need to look at the expressions you are using in the charts

Regards

Alan

Re: Re: Slow Qlik document

jochem_zw — Tue, 14 Oct 2014 20:36:18 GMT

You need to add a distinct to your Main_Dim

Main_Dim:

LOAD distinct CUSTCODE,

MainAccount,

Customer,

HOLDING_KEY_NEW,

Channels,

[Ds Ids],

CUSTOMER_ID,

RatePlanCode,

RatePlanName,

Segment

FROM

Main_Dim.qvd

(qvd);