Incremental QVD Build

Fri, 23 Jan 2015 16:09:10 GMT

Hello,

I'm working on a project that requires a large database table (300,000,000+ records) to be converted into a QVD file. If I attempted to load the entire thing into the QVD instantly, my computer locks up (it runs out of memory) and the script can't complete. Therefore, I devised the plan of loading X number of records from the table into the QVD at a time (where X is some number of records that my computer can handle).

This is done using the following loop:

DO WHILE (done = 1);

LET currentBreak = currentBreak + 1;

// Loads the next X records from the database

TABLE2:

LOAD * WHERE RECNO() >= ($(currentPointer($(currentBreak), $(rowsToLoad)))) AND RECNO() < ($(nextPointer($(currentBreak), $(rowsToLoad)))); // Loads all records between points X and Y

SQL SELECT * From database ORDER BY id;

// Loads the QVD and automatically concatenates it to the above selection from the Database.

TABLE1:

LOAD * FROM myQVD.qvd (qvd);

STORE TABLE2 INTO myQVD.qvd (qvd);

LET record = PEEK('id', $(nextPointer($(currentBreak), $(rowsToLoad))) - $(currentBreak) - 1, database);

DROP TABLE TABLE2; // Done to clear RAM

IF(len('$(record)') > 0) THEN

LET done = 1;

ELSE

LET done = 0;

ENDIF;

LOOP;

This works fine on small tables and has the expected functionality. The problem is that when I try to apply it to the large table I get the same 'out of memory' crash as when I try to load the table in a single pass, leading me to believe that QV is loading the entire table in the select statement and THEN filtering out all the records that don't fall between the two cutoff points.

My question is then how do I resolve this?

Re: Incremental QVD Build

MarcoWedel — Fri, 23 Jan 2015 16:25:20 GMT

LOAD * WHERE RECNO() >= ($(currentPointer($(currentBreak), $(rowsToLoad)))) AND RECNO() < ($(nextPointer($(currentBreak), $(rowsToLoad)))); // Loads all records between points X and Y
  SQL SELECT * From database ORDER BY id;

will indeed load all records from the DB and filter afterwards within QV.

Try to create the where part within the SQL statement.

Additionally you could create multiple small QVDs (one for each SQL Select) instead of one large QVD and load them like

LOAD *
FROM *.qvd (qvd);

in your application.

hope this helps

regards

Marco

topic Re: Incremental QVD Build in QlikView

Incremental QVD Build

Re: Incremental QVD Build