Performance tuning

[#1]

Hello All,

I am looking suggestions to improve the performance of my dashboard, below are the details of it.

Size - 200 MB approx (for now)

Front end - 8 tabs each with a Combo chart and 12 text boxes showing To date values and 2 containers with 3 straight tables each showing To date values.

Dev server config - 64 GB of RAM

The CPU reaches 100% as soon as the dashboard is reloaded and the rendering takes forever to complete.

I tried reducing the data to 100 MB and the rendering is comparatively better. I have changed the IF statements  to conditionally select on the Straight table and the Combo chart, using Link table and Autonumber for the Key generation.

Please give me your suggestions. Let me know if more information is needed.

Thanks.

[adamdavi3s]

To me what you're describing sounds like too much calculation in the front end rather than a volume issue.

Can you replaces the IFs with Pick(Match( as this is a LOT faster

Qlik should only be calculating what it can see so the fact you have 8tabs shouldn't matter.

For example we have a dashboard with maybe 20 tabs, 1.8bn rows of data, it runs about 5gb and the whole thing flies EXCEPT for one tab where we have calculations required at a field level. In this example we stop the chart calculating until the user has reduced their selection to 50k rows or less

[#3]

Thanks for your reply Adam, yes i replaced all the IF statements to Pick, but i could not feel any improvement in the performance though. So i tried to replicate the expressions and conditionally show them.

Either the RAM reaches the maximum or the CPU.

I analyzed the application through Document Analyzer and have modified the recommendations which was shown in it.

Any more suggestions?

For example, the below Pick statement is being used instead of the 'IF' across all the tabs multiple times,

=Num(Pick(Wildmatch(vCurr,'USD','LC','FXN'),Sum({<FISC_YR_NBR={"$(=Only({1}YearofToday))"},FISC_QTR_NBR={"$(=Only({1}QuarterofToday))"},FISC_DAY_OF_YR_NBR={"<=$(=Only({1}DayOfYearToday))"}>}PRC_USD_AMT)

,Sum({<FISC_YR_NBR={"$(=Only({1}YearofToday))"},FISC_QTR_NBR={"$(=Only({1}QuarterofToday))"},FISC_DAY_OF_YR_NBR={"<=$(=Only({1}DayOfYearToday))"}>} PRC_LCY_AMT)

,Sum(Aggr(Sum({<FISC_YR_NBR={"$(=Only({1}YearofToday))"},FISC_QTR_NBR={"$(=Only({1}QuarterofToday))"},FISC_DAY_OF_YR_NBR={"<=$(=Only({1}DayOfYearToday))"}>} PRC_LCY_AMT)

*(Sum(TOTAL<FISC_MTH_NBR>{<FISC_YR_NBR={"$(=Only({1}FXPrevYrToday))"},FISC_DAY_OF_YR_NBR={"<=$(=Only({1}FXDayOfYearToday))"}, CTRY_NAM,LOB_LVL1_DES,LOB_LVL2_DES,LOB_LVL3_DES, FROM_CCY_CD = p(FROM_CCY_CD)>}RATE_AMT)

  /Max(TOTAL<FISC_MTH_NBR>{1<FISC_YR_NBR={"$(=Only({1}FXPrevYrToday))"},FISC_DAY_OF_YR_NBR={"<=$(=Only({1}FXDayOfYearToday))"}>}FISC_DAY_OF_MTH_NBR)),LOB_LVL1_DES,LOB_LVL2_DES,LOB_LVL3_DES, FISC_MTH_NBR)))/1000000,'#,###.0')&' M'

Thanks.

[adamdavi3s]

It is quite hard to advise without having the app an analysing what is running slowly. I assume you have checked out the calculation time of the objects? (sheet properties > objects) and it is those with this expression which is slowing up?

Pick(Match( is a debate I have been having on another thread, it seems like it might not be as good as it used to be, or maybe only good in extreme circumstances.

Obviously you're using some set analysis in there for dates? can you do anything more clever with a canonical calendar?

[marcus_sommer]

I think you need to simplify your calculations more, for example by transferring parts of them into the script and quite probably you will need general changes to the datamodel - avoiding link-tables as far as possible, because they are from a performance point of view a bad choice - to reduce te number of tables which are included within the expressions and with them to reduce the volume of the virtual tables: Logical Inference and Aggregations.

- Marcus

[#6]

I can post the app here, but with limited set of data if that would help.

I might be able to use the Canonical calendar here as we follow a 446 calendar and it is already built into the database.

I analyzed my application through Rob Wunderlich's Doc Analyzer.

[#7]

My script has a link table because i have more than one fact table and the fact tables are at different granular levels

[marcus_sommer]

In general it's possible to match fact-tables with different granular levels - I couldn't say if it's possible and sensible in your case but you shouldn't discard this approach to fast - then your (main) goal is to optimize the gui and there aren't many alternatives to them.

- Marcus

[#9]

Also I have a dimension table which has to be related to one of the Fact tables. Yes i have read about the mixed granularity and generic keys, but am not clear it that could be used in my situation.