topic Re: How to avoid loading all QVD into memory in QlikView

How to avoid loading all QVD into memory

trust_okoroego1 — Fri, 06 Apr 2018 19:42:29 GMT

Hi,

I am running a test, and need to read just the first 100 rows from a 40 Million rows qvd file. Using the FIRST key word, the whole data will be loaded into memory first before qlikview will select the first 100 and likely drop the rest.

Is there a way like in SQL select statement to apply the limit and only load 100 record into memory?

Thanks for your support.

Re: How to avoid loading all QVD into memory

vishsaggi — Fri, 06 Apr 2018 20:48:11 GMT

Can you try like using Debug mode.

Press ctrl+E to open edit script then click on Debug

Then limit the load by 100.

Then click on run.

Re: How to avoid loading all QVD into memory

vishsaggi — Fri, 06 Apr 2018 20:51:18 GMT

Not sure if this helps may be try like

First 100

LOAD *

FROM yourqvdfilename.qvd(qvd)

Where 1=1;

Re: How to avoid loading all QVD into memory

trust_okoroego1 — Fri, 06 Apr 2018 21:10:44 GMT

Hi Vishwarath,

Thanks for your suggestion. I tried but it did not work

regards,

Re: How to avoid loading all QVD into memory

vishsaggi — Fri, 06 Apr 2018 21:21:28 GMT

What was the issue? Did you try the script I sent you?

Re: How to avoid loading all QVD into memory

trust_okoroego1 — Fri, 25 May 2018 19:30:43 GMT

Actually based on qlikview design, it is not possible as the where clause can only be applied after the data is loaded into memory. The suggestion of where 1=1 I guess is only effective when losing a qvd to avoid optimized load and rather read the record row by row.

Re: How to avoid loading all QVD into memory

marcus_sommer — Mon, 28 May 2018 08:41:00 GMT

On a QVD it's not possible because it's not a classical table else the data are stored within a data-table and multiple symbol-tables (one for each field). If you apply the reduction-logic from the debugger or FIRST on other file-formats like a csv it will stop after loading n records and speed up the whole load-process.

In your case you could use specially created source-files from the preceeding processes - for just a few simple testings it might be more expensive than loading the (reduced) qvd's, so it depends if it will be really helpful.

- Marcus

Re: How to avoid loading all QVD into memory

Miguel_Angel_Baeyens — Mon, 28 May 2018 08:46:04 GMT

You can try

LOAD *

FROM File.qvd (qvd) // or any other file

WHERE RowNo() < 100;

Re: How to avoid loading all QVD into memory

marcus_sommer — Mon, 28 May 2018 09:06:05 GMT

Good idea. But just tested and it's not faster by loading from a qvd else it adds some overhead respectively processing to the load and is slower than an optimized load of the whole data.

- Marcus

Re: How to avoid loading all QVD into memory

Miguel_Angel_Baeyens — Mon, 28 May 2018 11:20:38 GMT

Then it's using RecNo(). I cannot say for sure but we are using this almost every day on 1GB+ and 2GB+ QVDs just to check that the files are OK.

Re: How to avoid loading all QVD into memory

marcus_sommer — Mon, 28 May 2018 11:29:27 GMT

In my short test (QV 11.2 SR 12., various QVD's ca. 25 / 168 / 460 MB, run-time checking with now() in a variable directly before/after the load) I also tested on recno() and it was not really a difference to rowno() or the other methods. Are you sure that it is really significantly faster as an optimized load with the whole data?

- Marcus

Re: How to avoid loading all QVD into memory

Miguel_Angel_Baeyens — Mon, 28 May 2018 11:42:01 GMT

It was at least, we recently migrated to 12.10 and some things have changed drastically for better and some for worse.

The reason why we were doing this is to make sure that there was data in those QVDs and it has certain columns and rows, and dates as dates, etc. It's an extraction from SAP, rather sensitive, and we are using this as a sanity check of the load, and since there are developments and upgrades on the SAP side too, it's not surprising that the comma separate decimals appear instead of thousands, or the dates are DD.MM.YYYY instead of MMMMYYDD, and so on.

Also, the length of the file varies, from no lines at all to dozens of millions of lines. If after running a check on this "reduced" QVDs everything looks good, other tasks are triggered and eventually the full QVD set is loaded.

We could be loading the QVDs in their entirety, but when they are big, and the server is running another 12 tasks, resources can be an issue, therefore the "make-it-smaller" thing.

But again, that's our case. Loading a QVD and dropping it will not make any harm in other cases and in terms of speed, if it's just the QVD without any JOINs or other transformations, the time of loading 100 or 1000000 will be negligible.

Re: How to avoid loading all QVD into memory

marcus_sommer — Mon, 28 May 2018 12:16:04 GMT

Ok. I understand your usecase of pre-loading a reduced dataset and checking them before triggering the big tasks with potentially invalid data. But I think this meant that the data-checking is quite expensive and (by a potent storage-system) it won't make a big difference if a large qvd is first completely loaded and then reduced to n records before the checking are applied.

If there are no further quality-checkings like above mentioned it might be possible to make these checkings with the file-functions like qvdfieldname() and the xml-headers of the qvd which also include which formattings a field has whereby I'm not sure that it will be much faster and/or easier to implement or to use. Maybe it's an idea ...

- Marcus

Re: How to avoid loading all QVD into memory

Miguel_Angel_Baeyens — Mon, 28 May 2018 12:21:44 GMT

Agreed. File functions and metadata should be enough.

Storage is also a factor in our case with different disks in AWS, some of them fast some of them painfully slow for big files. So better loading a fraction of the file and if it's OK, then the rest.