4 Replies Latest reply: Feb 20, 2017 4:44 AM by Vivek Soundararajan RSS

    link table performance optimization

    ilan baruch

      hi all,

      a lot has been written in this community on differences between  link table to concatenation to solve several fact tables in single model.

      in my scenario i have 2 fact tables with same field names concatenated  into one. all is optimized and working well.

      when adding budget table (dim1, dim2, dim3, year, month, amount ) with a link table, budget table is very small and loading fast,

      but when loading fact table (resident load) into link table it hurt performance dramatically.


      any ideas on how to optimize this load, or another way to load budget into the model would be appreciated.


      advanced thanks.

       

        • Re: link table performance optimization
          Jonathan Dienst

          Do you have two fact tables already and you are adding a budget table? Or is the budget the second fact table? Are you referring to load performance or front end performance?

           

          My experience is that link tables can perform poorly in the front end if the data set is large and the link table's cardinality is high, so I would try to combine the fact tables into a single fact table with a source or type field to identify the different records.

           

          If the problem is reload performance, then you could consider an incremental load process to reduce the reload time.

          • Re: link table performance optimization
            Marcus Sommer

            Your performance issue while creating the link-table could be caused by a lack of available RAM - maybe you could change your load-order in some way for example by storing your fact-table into a qvd and then dropping the table and loading the link-table from the qvd - it should save some of the needed memory-space.

             

            Beside them it might be an alternatively if you concatenate your budget-data to your facts - no need to create a huge link-table and the performance within the UI will probably also be better.

             

            - Marcus

            • Re: link table performance optimization
              Vivek Soundararajan

              Hi Ilan,

               

              You mentioned that you are loading fact into link table, if you have fact table separately and dimension table separately there is no need of loading fact into link.

               

              According to me, you are taking dimension fields from Fact and concatenating those values into link table.

              Is my understanding correct?

               

              Also, Qlik stores temporary tables while concatenating statement are queued.

               

              For example,

              fact1 concatenate with fact2 will create a temp table1,

              further concatenating fact3 will create a temp table2

              which is concatenation of temp table 1 and fact3,

              it continues until last concatenate statement is found.


              After that it drops the temp tables and keep only the fact table.It takes huge memory to process this.

              So the load performance is affected here.


              Regards,

              Vivek