Optimized load

[maleksafa]

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)

[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.

[maleksafa]

the two qvds have different structure so the load order does matter ()

and i am respecting all the golden rules related to optimized load.

[Anonymous]

Malek

You say "...got 2 qvds that i am loading to the same table:"

Does that mean that the 2nd qvd you load concatenates onto the table created by loading the 1st qvd ?

If so then have a look at this post Turning Unoptimized Loads into Optimized Loadswhich describes optimizing loads that do concatenation.

[maleksafa]

that's exactly what i am doing, and i am following the rules mentioned in your link, and the qvd loads are optimized, however my problem is that the first qvd contains 70 million records and i was able to read it in less than a minute but for the second qvd it is taking 8 minutes to even start reading the qvd (although it is optimized)

below is the execution progress:

the firs qvd (FBSales) took only 40 seconds, for the second qvd it started reading after 6 minutes and took around 40-50 seconds.

[Anonymous]

Could you share the log file from the reload.

Also could you advise the size of the qvd's in Mbytes.

[tresesco]

I guess it is very much predictable and explainable. When you load the second table (that has different structure) and force concatenate it, the table structure of the destination table gets changed (it has to accomodate the null values for the additional fields). And so it takes time to structure the table even before the second table starts loading. Once it starts loading second table, it behaves as expected for an optimized load. That makes sense to me.

[Gabriel]

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

[maleksafa]

attached is the log file, FBSales.qvd is 2.5 GB and FBInventory.QVD is 820 MB.

[maleksafa]

thx for the reply, but actually this will not help as the load will no longer be optimized, because if i load all the fact table first, then the first qvd which does not have all the columns loaded first will not be optimized.