15 Replies Latest reply: Dec 30, 2014 9:50 AM by Steve Dark RSS

    Optimized load

    Malek Safa

      Hi,

       

      i have the below scenario that i need to solve:

      got 2 qvds that i am loading to the same table: FBSales (70+ million records) and FBInventory (8 million records), the execution script is as follow:

       

      1) Load FBSales, that takes only 1 minute to load (in optimized way)

      2) Load FBInventory, that takes 9 minutes to load (in optimized way) but the thing here is that if you check the execution script window the load of this qvd will start after 8 minutes from loading the FBSales, i guess it is something related to indexing.

       

      the only way to make the two loads optimized is if i load them in that order, first FBSales and then FBInventory, if i do it the other way around only FBinventory will be optimized but the load time will be faster than the above scenario.

       

      i have tried to first create the table by reading the FBSales and adding a where condition like 1<>1 in a way that it will create an empty table from the FBSales qvd, then read the FBInventory in optimized way but the FBSales is read in unoptimized way.

       

      any idea how i can solve this issue while keeping the two load optimized (considering that this was a test and the actual data will be around 150+ million records)

        • Re: Optimized load
          Gysbert Wassenaar

          If you load a qvd file and don't do anything other than

          • rename fields
          • omit fields
          • use a Where Exists clause on a single field

          then the qvd load will be optimized. The order of the loads does not matter.

          • Re: Optimized load
            Gabriel Oluwaseye

            Hi,

             

            The reason why the second load take 8 minutes to start loading is because of concatenation happening. 2nd load tries to concatenate with 1st load. In the process of concatenation QV will try and merge fields and if there are fields in 2nd load not in 1st or vice versa there would be delay.

            What you can try to optimized the load by having a dummy load of your actual FACT TABLE fields list  (using $Fields) and send to excel, load this before the 1st load and concatenate the 1st load and 2nd load to the FACT TABLE fields list LOAD.

             

            FACT TABLE fields list load should be autogenerate 1.

             

            Hope this help