help - performance improvements

[#1]

if i have a line of code like this:

if(a=b,1,

if(c=f,2,

if(c=g,3,4)))

my ?: if first condition is true (i.e.a=b) does the engine (parser) still waste it's time and slow stuff down by checking the remaining condiions?

[#2]

hi Erica,

I suppose the only way to know is to try it.

Thanks for your help.

Clive

[#3]

Thanks Clive, I'm finding this an interesting post so let me know if there are any performance improvements with that method!

Erica

[#4]

Like BMW IAM aggressive in my chase for better performance and  have hi ambitions in this regard. My (and all) unique ehSIGNature now renders in 0 secs, so ..., on to the next thing (in IT there's no rest for the wicked !)  I am determined  that every "app screen" will render in 0 secs. If the user (health consumer) selects Lung cancer then the time line uses icons (circles and pyramids) to give an indication of breach (e.g. target wait times exceeded) and how the team performed:

This pictures (and the one below) is taking >1 sec to render.

[#5]

The poor'ish performance when when rendering the timeline for a specific chip may be due to the lack of "short circuiting" (apologies if any confusion, but I speak a language which is a mix of Qlik technology and Big Health data, specifically NHS e.g. pathways, journeys, indicators, targets, periods of care, care activities etc.). Consider the list of periods for a Lung cancer pathway:

This picture renders immediately.

The pathway/time line for this is:

This is taking just over 1s to render (not good enough), and I think it's because it's running thru' code for ALL chips (consumer health integrated pathways) not just the Lung cancer, I will try and find the time to test out some of the suggestion made but I don't have the resource (I only need a bit), which when you consider what the NHS budget is is utter madness ...

[#6]

Hi Clive, I understand the complexities, I also work in the NHS! Do you work for a trust?

What expressions do you have in the chart?

Erica

[#7]

hi Erica,

I have worked for Barts and WLMHT in the past, but am currently unwaged and looking for paid work.

Which trust are you working for?

see below trail ...

I have dipped into the community to try and get some help, have discovered that with regard to expression code, the general View seems to be that Qlik does not “short circuit”,

(I have had some helpful feedback from some very bright people, however in depth understanding of the code is not my “specialty”, my specialty is big data.)

1. e.g. of no short circuit :

if(a and b and c,d)

If a is false it still checks b and c …

This is unfortunate.

The requirement:

• (see pict below This is myWorld – turning data into pictures J)

Look at the hi lited row at the top, what is happening:

An ambulance journey has come to an end, that ends (QUAntum particle u) the ambulance journey period (which can then be measure and I think has a target of 8 mins),  but it also starts (QUAntum particle 2d)

a wait period in A&E (target 4hours).

I am searching for the end of an ambulance journey and use the following code:

if a=1 and b=2 and substringcount(signActQUArkPeriodExpandedType,ehSIGN_Part02StartQk)>0

where the first substringcount parameter = 2d/CON20130622/A&E#u/CON20130622/AJ

and the second substringcount parameter = u/CON20130622/AJ

This is the only complex bit of code left now (it is repeated multiple times, often a<>1 or b<>1 but the code still gets executed, see above)

Can you suggest how to make it more efficient and help to get the render time<1s ?

Iam keeping the pictures (colours and shapes) simple, what’s behind them is complicated – you can’t get away from this, health is the most complex domain of all !

Putt the health consumer first, don’t cause unnecessary confusion, often people don’t want to know the detail, keep the pictures (the apps) simple!! (even if what’s behind them is complex).

see the following picture:

in 80% of the cases the first parameter only contains 1 part so in this I could do an = which would be much quicker than using the substringcount function ...

Are you working with a data warehouse or a more focused BI data mart?

[hic]

@ Rob Wunderlich, clive spindley, Magnus Åvitsland, Ralf Becher

I have discussed the "short circuiting" further with Håkan (the Inventor) now, and the reason why QlikView does't short circuit, is quite simple. Say that you have an expression

     If( <Condition>, <Expression1>, <Expression2> )

that is to be evaluated once per dimensional value.

If QlikView had evaluated the chart like a For-Next loop (one loop per dimensional value), then the Condition would first be evaluated and QlikView could choose which Expression then to evaluate. In other words, "short circuiting" would have been the obvious strategy.

But this would mean many aggregations - many passes over a large data set - and it would be very inefficient. Hence, it is not how QlikView evaluates a chart.

Instead, QlikView makes one pass over the data, calculating all three aggregations. These are then binned into the different dimensional values, whereupon the Condition is checked and the right Expression is used. Hence, the Condition is evaluated after the aggregation is made, and short circuiting is not possible.

The only improvement that could be made, is to check whether the Condition is an aggregation or just a check of a variable value, and use short circuiting for the latter case.

HIC

[#9]

That makes sense, one of the advantages of BI (in addition to being able to handle very large volumes of data)

is the power to aggregate (and eliminate this boring activity from the “to worry about list”).

It does of course mean working with the data at the appropriate level, the bottom level or the most complex

depending on your point of view and what excites you.

Thanks for the clarification,

Clive

[#10]

Hi Henric -

I've noticed you've put up a couple of blog posts on this topic, I'm glad you've shared it as I've found it an extremely useful insightand I think other users would too. Can I just ask, does the same apply to script if() in load statements  as for chart expression if statements?

And when it comes to the script control if..then...else..end does it do something similar? I've noticed if I put an "exit script" in an if statement, the loader sometimes picks up later items in the script.

Erica

[hic]

If you use an If() function in the script, you have the same problem - in principle. An If() will take some time to evaluate, and if used in a large table it will prolong the script execution. But it is not as bad, since a longer script execution often is acceptable. Further, you don't have any options for the script - you can't use Set Analysis.

The control statement if..then..else is different though: It is executed once, and that's it. As opposed to the If() function that may be executed millions of times - once per record.

HIC