topic Performance slow Qlikview for 30M+ Data only GeoMap Chart in QlikView

Performance slow Qlikview for 30M+ Data only GeoMap Chart

joybratas1 — Thu, 19 May 2022 12:32:22 GMT

Hi Community,

I am facing the same issue of performance with huge data. I have more than 30M records with weather data. I loaded directly from source.

It takes around 4-5 seconds before applying 1 filter. I am using a Geoanalytics map to show various events like Temperature, Humidity, Pressure etc. The Average, Minimum and Maximum with filters. O

I have used Nested If statement in the Colour expression and Label. I feel IF statement is making the process slow. I am pasting the equation I have used for the map.

Thanks

Sarco

Re: Performance slow Qlikview for 30M+ Data only GeoMap Chart

marcus_sommer — Thu, 19 May 2022 13:46:05 GMT

From a performance point of view are nested if-loops not a good choice and should be therefore avoided if any possible. In your case it looked as if the various calculations couldn't be simply reduced to a lower number but they might be outsourced within a variable. Means within the variable are the other variables evaluated and then the variable-result is the string of the appropriate expression. Maybe something in this way (simplified):

varEx: = if(var = 'x', 'max(value)', if(var = 'y', 'avg(value)', ...))

and within the chart it's called with:

$(varEx)

Beside this your aggregations itself contain an if-loop on the inside which is much slower as one on the outside and even this is much slower as a set analysis. Means something like:

max( if(date_x>=date($(vStartDate)) and date_x<=date($(vEndDate)), value ))

should be changed to:

max({< date_x = {">=$(vStartDate)<=$(vEndDate)"}>} value)

- Marcus

Re: Performance slow Qlikview for 30M+ Data only GeoMap Chart

joybratas1 — Thu, 19 May 2022 18:24:04 GMT

I tried the set analysis expressions. Yet no improvement of performance. The nested if statements outside will still create the issue.

Let me explain the data I hate

Staistics Field - Temperature, Humidity, Pressure, wind

Name Field- Min, Max, Avg

Value field- numeric values

So I am filtering based on Countries

Or based on Statistics Field

Or Name field.

So in any way I have to use a if condition to differentiate. Is there a way I can avoid the if here

-Joy

Re: Performance slow Qlikview for 30M+ Data only GeoMap Chart

marcus_sommer — Fri, 20 May 2022 06:04:19 GMT

At first make sure that the outsourcing of the nested if-loop is working, means that only one final expression-string is the output - and then applied to all relevant object-parts, means the main-expression and any title, label, coloring and so on expressions (you will probably need several variables for it).

To check the impact you may apply the old approach and the new logic within two table-charts. The differences between both objects may not huge but by 30 M of records I would expect to see them. If the optimized table-chart reacts too slow as be acceptable you need to go further and optimizing the data-model and/or even re-design the entire model.

If the above is working sufficient you may apply it again on your geo-chart. Geo-charts are differently to the normal charts and may need - independently from the amount of data and the complexity of the expressions - some time because their rendering could be very heavy - if all geo-layers will be calculated each time and they might be also quite granular and there might be also multiple wait/sleep-statements included.

Again you may do some checks with a dummy-app which contained beside your geo-data just a few hundreds/thousands of records and very simple expressions like sum(value). React it in real-time - just within a few milliseconds without noticing any delaying or does the whole (javascript) rendering of the geo-object needs some time?

- Marcus