3 Replies Latest reply: Jan 20, 2014 1:54 AM by Stanislav Jäger RSS

    How to use QV function WHERE exists for qvd optimized load

      Hi everybody,

       

       

      I'm not so sure if you
      already know and use following trick. The first time I was disappointed with
      loading performance from qvd using where condition.

       

       

      Example:

       

       

      load FIELD from My.qvd
      (qvd)

       

       

      WHERE
      FIELD2='Value_for_condition';

       

       

      Loading from big files took
      really long and condition caused not optimized load.

       

       

      I tried following:

       

       

      1) Define temporary table
      using load * inline [];

       

       

      2) using this temp table in
      condition as where exists ();

       

       

      Example:

       

       

      TEMP:

       

       

      load * inline

       

       

      [FIELD2

       

       

      Value_for_or_condition

       

       

      Value_for_or_condition2

       

       

      ];

       

       

      TABLE:

       

       

      load FIELD, FIELD2 from
      My.qvd (qvd) WHERE EXISTS ([FIELD2]);

       

       

      drop table TEMP;

       

       

      *******

       

       

      Well, this has certain
      issues.

       

       

      - Field used in a condition
      has to be selected as well, even if it is not needed (otherwise not qvd optimized)

       

       

      - two loads load *, x&z
      as y; load * from (qvd); -> is slow, not a qvd optimized

       

       

      - two condition fileds are
      not possible with where exists -> FIELD2='11' AND FIELD3='22' -> causes
      not optimized qvd load

       

       

      How do you handle more
      combined where conditions or transformation while loading from qvd file? (where
      data in qvd are not in the needed format for any reason)

       

       

      Regards

       

       

      Stan

        • Re: How to use QV function WHERE exists for qvd optimized load
          Sebastian Pereira

          Thanks for the idea! I didn't know this way to do an optimized load.

           

          So, if I understood, you are asking how you could do a combination of conditions. I think you could do something like:

           

          suposing that we will loading like:

          Load *

          From table.qvd

          Where (a=100 and b=78) or (b=500 and a=69);

           

          I guess you could do:

          TEMP:

          load * inline

          [a, b

          100, 78

          69,   500

          ];

          Left join

          Load Distinct a, b, a&'_'&b as a_b Resident TEMP;

           

          Table:

          Load *

          From table.qvd(qvd)

          Where exists (a&'_'&b, a_b);

           

          I dont know if this will be optimized, but you can try at less!!

          • Re: How to use QV function WHERE exists for qvd optimized load
            Srikanth P

            Hi , If you concatenate in the where exists function , it changed qvd load as un-optimized load. In this case in the QVD loader we need to create the composite keys.

             

            -------- QVD Loader -------

            TABLENAME:

            LOAD * , A&'-'&B AS KEY;

            SELECT * FROM TABLENAME;

             

            STORE TABLENAME into TABLENAME.qvd (qvd) ;

             

            ------- MAIN QVW --------

             

            TEMP:

            LOAD A & '-' & B AS KEY INLINE [

            A , B

            10, 100

            32, 165

            45, 90

            ];

             

            LOAD * TABLENAME.qvd (qvd)

            Where EXISTS(KEY);

             

            DROP Table TEMP ;

             

            This Load keeps Optimized load. If you are not doing any incremental load , use Autonumber functions for composite keys.

              • Re: How to use QV function WHERE exists for qvd optimized load

                Thank you for the reply.

                 

                I was thinking about the same composite key approach. However you can never know if there won't be a similar need with another concatenated key. Then there has to be another A&C field.

                In a selection, all possible combinations should be given, not just where Field (A,B,C) AND Field2(D,E,F). You should know then combinations like AD, AE, AF, BD, BE, BF ....

                 

                But I will keep it on my mind.

                 

                Regards

                 

                 

                 

                Stan