2 Replies Latest reply: Sep 16, 2013 12:06 PM by Antoine Frangieh RSS

    X records with n fields versus 1 record with (n + X) fields

    Antoine Frangieh

      Hi,

       

      For a model that I am building, I am trying to evaluate which is the optimal way to go forward.

       

      I need to create for each record to be analysed a set of parameters.

      So basically, table size + performance as always are of key interest.

       

      Intuitively, I lean for 1 record with (n + X) fields, with a key that links it back to my Fact table.

       

      Before I start simulating in order to assess the impact, does anyone

      - have a thought on the matter?

      - identify additional key interest areas

       

      These parameters will be :

      0 - generated in the load script

      1 - calculated based on the 10 fields (or a combination) that are given

      2 - used to generate additional parameters in the record

       

      Kind regards,

       

      Antoine

        • Re: X records with n fields versus 1 record with (n + X) fields

          Performance depends on both the number of records and the number of fields.  I would not recommend a fact table with 10 million rows, but 300 fields.  I've even seen a case where having 600 fields caused a weird error in QlikView. 

           

          The worst performance results have occurred when the model contains 2 tables with a large number of records and fields.  For this reason I stopped using link tables long ago, but your question is a good one.  I don't know what has more effect on performance: a large number of fields or a large number of columns. 

           

          Look forward to the results.

           

          Karl

          • Re: X records with n fields versus 1 record with (n + X) fields
            Antoine Frangieh

            I managed to split the problem in half.
            I generated the minimum required records and the minimum required fields.


            When I went to full blast (i.e. all records) I generated out of 30k 16 mio with a size of 150 MB.

            Execution time 11 min.

             

            When I divided the problem, I kept a 500K records with additional 23 fields.  Size 6MB

            Execution time less than a minute.

             

            Now for the next part of the riddle, which is how to automate the creation of the 23 fields.

            http://community.qlik.com/thread/92181