Set Analysis with OR Condition - performance

[settu_periasamy]

Hi All,

I have the set analysis expression like the below using OR Conditon.

There is no problem in this expression, but the response time is very high  (it takes almost 5 mins to display the result )

i'm having more than 10 millions of records.

Count({1}{<Table={'101','100'},Type={'Fixed','List'},VALFROM={">$(vFrom)"}>

          +

      <Table={'101','100'},Type={'Fixed','List'},VALTO={"<$(vTo)"}Product)

At the same time, i tried to separate this expression like,

Count({1}{<Table={'101','100'},Type={'Fixed','List'},VALFROM={">$(vFrom)"}>}Product)

Count({1}{<Table={'101','100'},Type={'Fixed','List'},VALTO={"<$(vTo)"}Product)

    I get the immediate result for above expressions.

Could anyone give the suggestion to improve the performance for this?

Edit:

I tried with if condition like

if(VALFROM>$(vFrom) or VALTO<$(vTo),

          Count({1}{<Table={'101','100'},Type={'Fixed','List'}>}Product) )

This is bit increase the response time compare with OR in set analysis.

So, Just i ask you, shall i use the if condition? or do you have any idea to do this in  set expression with OR ..

Message was edited by: Settu Periasamy

[Siva_Sankar]

Settu,

Try using flags in the script level and use those flags in your expression. Refer Optimize UI Performance with Flags and Set Analysis

[vikasmahajan]

While Loading data you can create flags and use this flags in set anlysis will improve the performance and effetive coding as per shankarece link.

Vikas

[jonathandienst]

I assume you are trying to get records between a pair of dates. Your expression is using a union, and so very likely it is trying to return everything from the table. Corrected syntax would be:

=Count({1<Table={'101','100'}, Type={'Fixed','List'}, VALFROM={">$(=vFrom)"}, VALTO={"<$(=vTo)"}>} Product)

But are you sure that you want to ignore all selections in this expression? Perhaps you need it without the '1'

=Count({<Table={'101','100'}, Type={'Fixed','List'}, VALFROM={">$(=vFrom)"}, VALTO={"<$(=vTo)"}>} Product)

If you want to do it using 2 set expressions, you need an intersection like

Count({1<Table={'101','100'}, Type={'Fixed','List'}, VALFROM={">$(=vFrom)"}> *

  1<Table={'101','100'}, Type={'Fixed','List'}, VALTO={"<$(=vTo)"}>} Product)

(Note the 1 applied to both expressions), or:

Count({<Table={'101','100'}, Type={'Fixed','List'}, VALFROM={">$(=vFrom)"}> *

  <Table={'101','100'}, Type={'Fixed','List'}, VALTO={"<$(=vTo)"}>} Product)

But these are functionally the same as the simpler expressions above.

[settu_periasamy]

Hi Jonathan,

Thanks for your time.

i'm not trying to get the records between of pair of dates. Actually i need to display the records, if VALFROM > somedate(eg. 2020-01-01) OR if VALTO < '2000-12-31'.

My expression gives the expected result. But the response time is very high.

Thank you for your suggestion.

[settu_periasamy]

Thank you Vikas.

[settu_periasamy]

Thank you Shankar.

[jagan]

Hi,

Move this into set analysis like below, set analysis will be faster.

Sum({<VALFROM={'>$(=vDate1)'} >+ <VALTO = {'<$(=vDate2)'} >} Value)

Assume that vDate1 and vDate2 are variables holding the dates.

Regards,

Jagan.

[settu_periasamy]

Hi Jagan,

Already i tried this (i have used Count the product instead of sum). But the response time is very very slow.

that's why , i moved from set expression to IF condition.

like

if(VALFROM>$(vFrom) or VALTO<$(vTo),

          Count({1}{<Table={'101','100'},Type={'Fixed','List'}>}Product) )

- This expression gives the better performance compare with Set expression.

Thanks Jagan.

Regards,

Settu P

[jagan]

Hi,

It should not be like that, if those dates are fixed or able to arrive a flag in the script, then create a flag and this in Set analysis.

Hope this helps you.

Regards,

Jagan.