Increasing QVD sizes when loading and storing in a long loop

[Oleg_Troyansky]

Hi everybody,

I'm experiencing a strange phenomenon, and I'd like to ask if anyone had seen the same, and what can be done about it.

I have a Qlik Sense script that is running over hundreds of folders (using the famous DoDir logic) and transforming thousands of Text files into QVD files.  All text files from a certain folder are stored in one QVD file.

So, in a nutshell, the overly simplified logic is the following:

FOR EACH ...

     FOR EACH ....

          Table1:

          LOAD ......

    NEXT

     STORE Table1 INTO .....

     drop table Table1;

NEXT

We noticed that the size of each QVD file goes and grows, even though the volume of data in each QVD is comparable. If the first QVD file could be approx. 20MB in size, the last QVD file was about 80MB in size. The volumes of data are comparable - in fact, the smaller QVD file in the beginning of the run contained more data than the 4-times larger QVD file from the end of the run.

I have a feeling that it has to do with the size of the index that's being built. However, since I'm dropping every table immediately after storing it, I don't know what else I can do to prevent the growth of the data size.

Any ideas about the reasons for this issue and possible solutions?

I'd love to explore a possibility of spinning out separate tasks and loading one folder at a time, but I don't know how can I do it in Qlik Sense without the ability to pass parameters to the script.

appreciate your thoughts,

Oleg Troyansky

[marcus_sommer · Accepted solution]

I could imagine that this behaviour will be caused from the qvd-header and that the lineage-data or something similar increase and won't be dropped with the dropped table. I remember some postings about problems with them but I couldn't find them unless this old stuff: Re: QVD File Size in QV11.

- Marcus

[sunny_talwar]

Had a similar trouble in QlikView... not sure if there are similar settings in Qlik Sense... but we used this

Expansion of QVD file size during iterated process

[Oleg_Troyansky]

Interesting development...

During this process, I was also collecting the names of the processed files in a separate table that had no common fields with the main data table. At first, I was collecting all the file names for the whole run, and I was storing a single text file as a "log" at the end of the processing.

Later on, I changed the process of logging - instead of writing a single log at the end, I started writing smaller logs when each QVD file was stored, and I was dropping the log table immediately after. This time, the QVD file sizes didn't grow. In total, the summarized QVD size was about 5 times smaller compared to the first run with a single "log" table.

What keeps me puzzled is that the main data table had nothing in common with the log table, and yet managing a growing log was causing the growing size of the "main" QVD files, while managing many smaller logs helped keeping the "main" QVD sizes smaller...

Never stop learning...