8 Replies Latest reply: Nov 21, 2016 11:18 AM by Ganesh Bagavathi RSS

    Performance tuning

    Ganesh Bagavathi

      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.

        • Re: Performance tuning
          Adam Davies

          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

            • Re: Performance tuning
              Ganesh Bagavathi

              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.

                • Re: Performance tuning
                  Adam Davies

                  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?

                    • Re: Performance tuning
                      Ganesh Bagavathi

                      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.

                    • Re: Performance tuning
                      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