Efficiency Question

[sunny_talwar]

So I have built a very long expression (500 lines). This particular formula is calculating the some sort of Average. Now I want to use the same exact formula, but calculate Min and Max and then toggle between Avg, Min & Max. To do this I can think of three different methods, but not sure which would be the most efficient method out of the three:

Method 1:

Add 3 Tables on top of each other one each with Min, Max and Average and toggle between the three of them. So when vSel = 0 show Avg Table, vSel = 1, show Min Table, vSel= 2, show Max Table.

Method 2:

Create a variable vAggr = If(vSel = 0, 'Avg', If(vSel = 1, 'Min', 'Max')) and use this in my expression as $(vAggr)(myField)

Method 3:

Create three expressions within the same table and conditionally show each expression. So when vSel = 0 show Avg Expression, vSel = 1, show Min Expression, vSel= 2, show Max Expression.

There might be other ways and I am flexible to use the best (most efficient) way. Can somebody help me determine which method would be most ideal to use.

Right now the there is only 100k rows of data, but in the near future it will be millions of rows of data.

Thanks in advance for your help on this.

Best,

S

[Gysbert_Wassenaar]

500 lines sounds rather extreme. Perhaps you can simplify that expression itself. If the expression contains a lot of if statements perhaps you can replace them with pick-match combinations or maybe other functions. And perhaps you can do something in the script so calculations are done there instead of in the front end.

[Anonymous]

sunindia,

I would try 3 or 1, but I think 3 is better in order you have it all together in one object. About 2, i'm not sure...

Marc.

[sunny_talwar]

Hi Gysbert,

Thanks for your prompt response.

I agree that it can be simplified by using variables because this formula is basically testing things across different expression to yield a result. I def. want to spend time on making that more efficient. But currently I am more concerned about having to decide the most efficient way to calculate Min, Max and Avg.

Best,

S

[sunny_talwar]

Hey Marc,

Thanks for your response.

I am using Method 1 right now and with 100k rows, it isn't a big deal. Not sure how it will behave when more data will be added.

My concern with Method 3 is that there are 10 expressions for Avg, 10 for Min and 10 for Max (not really sure if Method 3 will be a good option to go with). But I may very well be wrong.

Is there a way to test which method is the best? Does Calc time for object within sheet properties a good way to gauge performance?

Best,

S

[mvanlutterveld]

Hi,

To make your expressions as efficient as possible prepare them as far as you can in the script. Especially when your data model will contain millions of records this is an important step in getting an acceptable performance. In this way you can also narrow down the number of lines needed for your expression (500 lines is way too much).

The three methods you suggest are merely different presentations of the same. I would opt for method 3 as IMO this is the most user friendly. Create in your data model an inline table with clear descriptions for the Min, Max and Avg presentation. In the front end create a list box based on those descriptions (be sure to select ‘Always one selected value’) and show the expression for Min, Max and Avg conditionally based on the selection made in that list box. In this way the end user is able to select the wanted expression.

Michiel

[Anonymous]

Try with DocumentAnalyzer v2.3, objects tab, you will find info about reload time. Maybe useful.

Marc.