topic Re: Optimized load in QlikView

Optimized load

maleksafa — Mon, 26 Jan 2026 18:19:17 GMT

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 — Mon, 29 Dec 2014 15:51:36 GMT

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

maleksafa — Mon, 29 Dec 2014 15:57:44 GMT

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

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

Re: Optimized load

Anonymous — Mon, 29 Dec 2014 16:05:59 GMT

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.

Re: Optimized load

maleksafa — Tue, 30 Dec 2014 08:03:43 GMT

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.

Re: Optimized load

Anonymous — Tue, 30 Dec 2014 11:28:45 GMT

Could you share the log file from the reload.

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

Re: Optimized load

tresB — Tue, 30 Dec 2014 11:41:26 GMT

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.

Re: Optimized load

Gabriel — Tue, 30 Dec 2014 11:46:40 GMT

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

Re: Optimized load

maleksafa — Tue, 30 Dec 2014 11:52:46 GMT

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

Re: Optimized load

maleksafa — Tue, 30 Dec 2014 12:01:02 GMT

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.

Re: Optimized load

maleksafa — Tue, 30 Dec 2014 12:22:05 GMT

but that's very weird for an optimized load, because if i load the qvds the other way around, only one of them will be optimized but the overall load time will be much faster.

Re: Optimized load

tresB — Tue, 30 Dec 2014 12:29:12 GMT

That is probably because, qv takes more time to resturcture a table than this unoptimized load. I did not test it, still it seems that this case (loading your second your table fast) would only be faster iff the second table is a subset of the first one. Because, that would lead not restructure the first table.

Re: Optimized load

Anonymous — Tue, 30 Dec 2014 12:54:24 GMT

Malek

What you need to do is when you create the 2 source qvd's, add the extra fields in with values of null. Such that both your input qvd's have the same structure.

This was recommended to me by stevedark and I have used it and it works perfectly for me.

Re: Optimized load

maleksafa — Tue, 30 Dec 2014 13:08:36 GMT

i will give it a try, but adding null fields to the second load will make it not optimized, so i guess it is a compromise to take between optimized load and this.

Re: Optimized load

Anonymous — Tue, 30 Dec 2014 13:17:59 GMT

Malek

That was not what I meant.

What I meant was when you create the 1st source qvd add the fields as Null's that are in the 2nd qvd but not this 1st qvd.

Then when you create the 2nd source qvd add the fields as Null's that are in the 1st qvd but not this 2nd qvd.

Such that both the resulting 1st and 2nd qvd 's have identical structures.

Re: Optimized load

stevedark — Tue, 30 Dec 2014 14:50:11 GMT

Thanks for the mention Bill.