topic AW:loading data from QVD in QlikView

loading data from QVD

Wed, 26 May 2010 09:25:29 GMT

Hi All,

I have a qvd with 7.9M rows, I'm trying to load the data which is grouping and summing and the final result should leave me with a dataset of 4.9M rows.

However the script runs for a few seconds, the memory usage goes crazy and then I get an error saying that I'm out of virtual memory and the load stops.

The thing is I thought my code is pretty simple and am surprised QV can't cope, is this normal behaviour or is there a problem with my code - posted below?

total:
unqualify *;
LOAD
 AAGRNUM,YEAR,MONTH,
max(DUEDATE) as DUEDATE,
sum(ASFINC + DOCFEE + LAIINC + NON_INS_INC + PRIDEPN + PRIRENT + RPWREC + UN_RPW + GLR + RV + ASFREC + DOCREC + LAIREC) As TOTAL
FROM qaim04.qvd (qvd)
GROUP BY AAGRNUM, YEAR, MONTH ;

Many thanks,
Phil

AW:loading data from QVD

biester — Wed, 26 May 2010 11:27:43 GMT

I couldn't see anything spectacular in the code, so I tested with autogenerated random sample data (30.000.000 rows, which resulted in ca 1,500.000 grouped rows) but the thing was working without problems; memory consumption went up to ca 1,5 GB though, but it worked, the whole reload (generating table, writing it to qvd, rereading it from qvd and grouping it lasted ca 10 minutes. Of course it COULD be a memory issue with lots of data. If I'd run my test app with let's say 100 M instead of 30 M of data I also might have an error. Perhaps you could try to decrease the quantity of rows stepwise, then, if it IS a memory issue, you should come to a point where you don't have the virtual memory error any more.

Rgds,
Joachim

AW:loading data from QVD

Wed, 26 May 2010 11:47:17 GMT

Hi Joachim,

Many thanks for the reply, it may be memory as my PC bombs out when memory usage gets to just under 1Gb .. the spec is a 2Gb dual core processor.

Though as you say the code isn't doing anything too complex and having to break it up just increases development time and makes the process more complex, though sadly it looks as though I haven't got a choice as this is the only environment I have for development.

Kind Regards,
Phil

AW:Re: AW:loading data from QVD

biester — Wed, 26 May 2010 11:59:59 GMT

Perhaps you don't have to split up anything; maybe using the FIRST statement (e. g. FIRST 100000 LOAD) to reduce data during development the final environment might help.