4 Replies Latest reply: Jan 15, 2013 11:27 AM by Juan Lopez RSS

    Load All (*) vs. load field1, field2,...,field(n)

    Juan Lopez

      Hi ppl!

       

      In SQL Server, the execution plan for a

       

      Select * from table

       

      is different to the one for

       

      Select field1 from table

       

      (assuming "table" only has one field)

       

      so the question is if that is also a concern in qlikview where we can load

       

       

      Load * FROM $(vRutaDatos)Base_EXT.qvd (qvd);

       

      or

       

      Load field1, field2 FROM $(vRutaDatos)Base_EXT.qvd (qvd);

       

       

       

      I'm showing here the QVD version, since I think when loading from a DB the same concept should be applied (form the sql server example) since the query is taking place in the server, not in qlikview.

      any other weird cases in which you can optimize the reading are welcome too!

       

      any light on this would be appreciatted, thx for your time

        • Re: Load All (*) vs. load field1, field2,...,field(n)
          Henric Cronström

          There shouldn't be any difference between the two - unless the wildcard (*) implies a larger number of fields.

           

          I personally try to avoid the wildcard if I know that the source file may change, e.g. columns may be added.

           

          HIC

          • Re: Load All (*) vs. load field1, field2,...,field(n)
            Deepak Vadithala

            Hi,

             

            You are right! In SQL Server execution plans are different for "*" compared to explicit field names. You might not use Index Seek operator if you are using "*". And you might not use planned cache with "*".

             

            I agree with HIC and I avoid using "*" in QlikView for the following reasons. Although, there will not be performance implications if you have same number of fields.

             

            1. You will loose control over number of new fields added while you are using "*"

            2. You will never know if the table schema is changed while you are using "*"

            3. It becomes difficult when you are using QlikView Joins with "*" because of Join Predicate works on matching fields

            4. QlikVIew implicit joins based on common field names and it becomes difficult to understand for unwanted joins

            5. If you are using "*" and you wanted to perform transformation on a field then you are duplicating the field as a new field instead of transforming the same field

            6. Last but not least - It looks like a lazy programmer/developer

             

            Cheers,

            DV

             

            www.QlikShare.com

            • Re: Load All (*) vs. load field1, field2,...,field(n)
              Gabriel Oluwaseye

              Hi Lopez,

               

               

              First and foremost, the point stated above (from HIC & Deepak) is the best. What I would also add to that is a field might be blank and if you used * in your query, from DATA MODELLING point the field is redundant and of no use. So let us caltivate the ability of specifying fields needed.

               

               

              Regards,

              Gabriel