topic $ Expansion Performance in QlikView

$ Expansion Performance

Sun, 30 Aug 2009 16:02:35 GMT

Hello,

I am interested in your experience with the performency of $ expansion.

I have a created s set um formulas which I use frequently in my system.
Too bad, the formulas differ slightly, esp. in the usage of the set-analysis.

No problem, I created variables which hold the formula, and I pass the necessary set-analysis parameters at run time.

Example:

Normally the function in an expression would be like this: =Sum({<Year={2009}>} MyField)

I created a variable 'calcSUM' as '=Sum({$1} MyField)'.
In the expressions I use =$(calcSUM(<Year={2009}>))).

This works great - I even use nested 'functions' by nesting $ expansion (e.g. to keep the variable name flexible).

But I wonder about performance:
How fast is $ expansion?
How often is it done, when I use it in an expression (i.e. once per pivot-table, or for every row/column?)

What is your experience?
Thank you,
Thilo

$ Expansion Performance

johnw — Tue, 01 Sep 2009 01:17:49 GMT

I don't have experience with this personally. From a naive "how I would probably design it" point of view, I wouldn't expect there to be more than a fraction of a percentage point difference in speed. I would just do the expansions, then I would execute the expanded formula across all rows. Doing the expansions should take almost no time compared to executing the expanded formulas across all rows. So I wouldn't expect the expansions to cause any performance problems.

That said, I have HEARD that nested expansions DO cause performance problems. Which to me suggests that QlikView might be doing it for every row/column as you asked.

What you can do is test the performance for yourself. On one new tab, copy your chart with the nested $ expansions. On another tab, make the same chart with the fully-expanded expressions. Make sure that QlikView doesn't open to any tab with any copy of the chart. Close and reopen QlikView to make sure nothing is buffered. Click one of your new tabs. Go to settings, document properties, and check the CalcTime of the chart. Close it down and repeat with the other tab. Do it a few times to make sure your results are statistically-significant.

However, I should also give you a warning from a private communication with a senior support technician at QlikView:

"First, one might want to point out that the CalcTime numbers should be used with caution, as they might be very cumulative. Therefore, we've made our tests on your data (in 10, 100 and 500 million batches; the numbers below are in 500m) using a macro which in a simple way records the "true" calculation time, and also the full CPU core time spent, using ActiveDocument.GetApplication.GetElapsedMilliseconds and ActiveDocument.GetApplication.GetCpuMilliseconds. With these values one can also get a ratio of the CPU core efficiency (total cpu time / elapsed time) , which has been one of the issues (not included here)."

So if you want to be certain that your performance numbers are accurate, you're going to need to write some macro code. I haven't fiddled with that yet myself.

$ Expansion Performance

IAMDV — Tue, 05 Apr 2011 11:09:14 GMT

Hi John,

First let me thank you for detailed explanation. I totally understand that the above post is very old. However, I am very keen to know if you had got chance to work on the Macro to measure the performance. Any direction will be greatly appreciated.

Many thanks in anticipation.

Cheers - DV

$ Expansion Performance

johnw — Tue, 05 Apr 2011 17:47:22 GMT

Nope, I never coded a macro to more accurately measure performance. I really should put that on my list of things to do. I don't think about it when I don't have anything to measure, and when I do have something to measure, I tend to be in too much of a rush to try to figure out how to do it correctly. I'm swamped at work though, so it could be a while even if I do get to it some time.

If you want to do it yourself, here's my guess on how you'd do it:

start with the chart you want to measure hidden
e1 = elapsed milliseconds
c1 = CPU milliseconds
unhide the chart
wait for idle
c2 = CPU milliseconds
e2 = elapsed milliseconds
set a variable to c2 - c1
set a variable to e2 - e1

For testing, I'd try out some really slow charts and see how the elapsed time compares to checking the clock. For CPU, I'd do the same, but tracking CPU % in task manager and estimating the average during the time the chart calculates. Multiply by the elapsed time, and you should get something close to the elapsed CPU. I'd also compare elapsed time to the reported calc time for the chart, and again, they should be close as long as there's nothing else going on with the tab.

If you get the macro working, please post it. 🙂