6 Replies Latest reply: Nov 25, 2016 6:05 AM by Friedrich Hofmann RSS

    Make a LOAD optimized - with a >= - plz help

    Friedrich Hofmann

      Hi,

       

      I know the question of optimized vs. non-optimized LOAD has been here a lot of times. I've read up on it a little and if there was a filter for one specific value, it might make sense to load this into a small helper_table and then use a WHERE_EXISTS clause to make the main_LOAD optimized - but here I don't have one specific value: Rather I have a date and I filter a very big table for only the records with a date equal to or later than that - I have the ">=" operator.

      => Is there any way I can make that an optimized LOAD (from a qvd)?

       

      Thanks a lot!

       

      Best regards,

       

      DataNibbler

        • Re: Make a LOAD optimized - with a >= - plz help
          Bill Markham

          You could generate a table of all the dates that you need to load.

           

          I do this often and it works great.

          • Re: Make a LOAD optimized - with a >= - plz help
            Liron Baram

            hi

            you could create a list of the dates

            and then use where exists

            to create the list of the dates you could use this script

             

             

             

            let vDate = date('01/11/2016');

             

             

            load '$(vDate)'+IterNo()-1 As Date

            AutoGenerate 1

            While '$(vDate)'+IterNo()-1 <=today();

              • Re: Make a LOAD optimized - with a >= - plz help
                Friedrich Hofmann

                Hi Liron,

                 

                that's great. But then I'd have a LOAD with a WHERE_EXISTS clause with more than one value in it - would that still be optimized?

                Hmmm ... yes, I just found a thread that suggests it would be an optimized LOAD. Let's see if it improves the performance.

                  • Re: Make a LOAD optimized - with a >= - plz help
                    Friedrich Hofmann

                    Oh - I just remember, there is a second filter, so I guess it would not be optimized - or, maybe I could make it, but only for the smaller part:

                    - For one part of that big table, the smaller part, there is a filter for one specific field (TRANCODE) for one specific
                       value, SPRECEIVE

                    - For the other part, the filter is for the field having any other value - there are several other possible codes.

                     

                    Hmmm ... but there is one step before that, the big table from which I load is put together from the archive and the current table in another app that runs once an hour. Maybe there I could create one more field with just a 1/0 value for that field.

                    Let's see. I'll go step by step.

                      • Re: Make a LOAD optimized - with a >= - plz help
                        Friedrich Hofmann

                        Hmmm ... it's not an optimized LOAD anymore. Strange. There are no compound keys anymore, I have moved that process into the preparatory app that runs once an hour.

                        Well, then it doesn't improve the performance. Too bad. That one table that has to be prepared is the last "big fish" rgd. the performance of that report - if I could make that any faster, it would improve the whole thing.

                         

                        Then there is one more thing I can go after - at some point, the nr. of records I have is multiplied - I have many more records in my table than are actually displayed in the straight_table_diagram on the GUI. That also makes the whole thing bigger - and slower - than it need be.