Performance Issue by Using "&" in Set Analysis

[amit_saini]

Hi Folks,

I'm using below expression in a bar chart:

count(DISTINCT {<Month=,Week=,Day=,Type_Name=,Why_Name=,Reason_Name=,Manning_Compensation=>}Station_Label & Line_Name)

It is business requirement to take distinct count of  Station_Label & Line_Name together , which is impacting chart performance.

Is there any way to write this logic without "&"  ???? As it is killing chart performance.

Regards,

AS

[marcus_sommer]

In addition to the approach of bringing both fields within one table you could improve the performance if you used numerical ID's instead of string-values for your counting - maybe creating them additionally as an autonumber-field or you used dual() to assign a numerical value to the string.

After this you could use these ID's or num(DualValue) and it should be already faster. More speed might be possible if you then replaced the & with a + like:

count(distinct Field1 * 10000 + Field2)

The 10000 is just an example that you need any measure to assure that a summing of both fields will always return a unique value and if any of both fieldvalues is NULL the + operator makes it complete NULL (if you also want to count these values just take a rangesum() instead of the +).

- Marcus

[amit_saini]

I tried above code and performance is far much better.

Thanks,

AS