Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
chesterluck
Contributor II

DLP optimization - not accaptable query execution time

Hello

I've build a report, but unfortunatly the query execution time is just too long - over one minute.

I've tried already to optimize the model, but without any success.

The biggest table I have is about 500'000'000 rows. And I have tested following models:

Model1

Model2:Model 1.png

Model 2.png

The last thing which might help is to join calendar to the fact table...

Otherwise I see only the way to split the table in different parts and create different reports.

Actually I did it already and split it in years, but the execution time is only acceptable when I split it in 4 month periods (execution time around 8 sec).

What can I do in order to optimize the model/report/execution time???

Any ideas?

cheers chesterluck

1 Solution

Accepted Solutions

Re: DLP optimization - not accaptable query execution time

Hi,

     Yes it will sure help.

     Try to transfer all your calculations which you are doing at chart level to script.

     Also make sure that you are not using the count(),Count(if...), sum(if...), if(..,count())... statements in charts.

     this are listed a extreme resource intensive functions.

Regards,

Kaushik solanki

View solution in original post

7 Replies
jsn
Honored Contributor

Re: DLP optimization - not accaptable query execution time

Hi chesterluck,

Maybe the long execution time lies on the front end side of things with complex expressions?

Any specific objects or metrics that take a long time to calculate?

What kind of hardware are you running this huge app on?

chesterluck
Contributor II

Re: DLP optimization - not accaptable query execution time

Hi Johannes

I dont think its because of the front end. There is only one diagramm with xAxis= MeasureTimeStamp and yAxis=ActualValue.

The explained data is data from production-machines and its huge because every part produced brings a lot of parameterdata along with it.

Hardware: Intel® Xeon® Processor E5645 @2.4 Ghz @2.4 Ghz (6Core) And 16 gb RAM

cheers chesterluck

jsn
Honored Contributor

Re: DLP optimization - not accaptable query execution time

Oh, ok, so when you say "query execution time" you refer to the time it takes to run the script when hitting reload?

Or do you mean the time it takes for the chart to calculate in the front end?

chesterluck
Contributor II

Re: DLP optimization - not accaptable query execution time

Well basically both are taking too long, but im referring to the 2nd issue:

The chart calculation takes too long

Cheers chesterluck

chesterluck
Contributor II

Re: DLP optimization - not accaptable query execution time

While searching through some similar threads I found a document analyzer:

http://robwunderlich.com/Download.html

And I've executed it on the document.  Hope it helps:

ClassTypeSubTypeIdCountSizeBytes
2055203321
DatabaseTableRecordsFact_Stichprobe119007943131547103259
DatabaseTableRecordsStichprobe1996257410199625740
DatabaseFieldSymbols#Stichprobe181845738145476584
DatabaseFieldSymbolsMesszeitpunkt139257508111406000
State SpaceField StateState#Stichprobe11818457318184573
State SpaceTable StateInternalFact_Stichprobe 14876000
State SpaceField StateStateMesszeitpunkt11392575013925750
State SpaceTable StateInternalStichprobe 2495328
DatabaseFieldSymbolsdActVal1050268840208
SheetobjectListBoxInternalDocument\LB12 840208
SheetobjectStraightTableBoxInternalDocument\CH07 151976
State SpaceField StateStatedActVal1105026105026
DatabaseFieldSymbolsJob245117.0241706
chesterluck
Contributor II

Re: DLP optimization - not accaptable query execution time

Please verify the following idea:

The main problems are fields Stichprobe (number of a sample)  and Messzeitpunkt (timestamp).

What I can do is:

1) Format the Messzeitpunkt already in the DLP. (Until now I'm formating it in the diagramm

 

Time(Messzeitpunkt, 'YYYY.MM.DD hh:mm')

)

2)  Stichprobe is just a number and anyhow we are not searching for it. It is also not unique as it contans also sample measurement numbers. So basically I can at the end just leave it out.

3) In case I cant leace the Stichprobe out - I can create an autonumber...

What do you think - will it help?

Re: DLP optimization - not accaptable query execution time

Hi,

     Yes it will sure help.

     Try to transfer all your calculations which you are doing at chart level to script.

     Also make sure that you are not using the count(),Count(if...), sum(if...), if(..,count())... statements in charts.

     this are listed a extreme resource intensive functions.

Regards,

Kaushik solanki

View solution in original post