topic Re: Filtering QV recrods in memory is slower than loading from file in QlikView

Filtering QV recrods in memory is slower than loading from file

Fri, 07 Dec 2012 07:12:50 GMT

I have the below code which will

Read a qvd file,
Filter the data and
Store it to another qvd file.

IN_TABLE:
LOAD *
FROM [.\INPUT.qvd] (qvd);
OUT_TABLE:
LOAD *
Resident IN_TABLE
WHERE DATA_TYPE_ID=200;
Store OUT_TABLE into [.\OUTPUT.qvd];

But during the filtering process (where statment), it scan thur the IN_TABLE very slowly.

Am I doing it wrong?

Is there any way to speed up the filtering?

Re: Filtering QV recrods in memory is slower than loading from file

Gysbert_Wassenaar — Fri, 07 Dec 2012 07:25:07 GMT

A resident load can be slower than a direct load from qvd. Try:

IN_TABLE: LOAD * FROM [.\INPUT.qvd] (qvd) WHERE DATA_TYPE_ID=200; Store IN_TABLE into [.\OUTPUT.qvd];

Re: Filtering QV recrods in memory is slower than loading from file

Fri, 07 Dec 2012 07:46:01 GMT

Thanks for the reply.

I have tried it, but QV will still load the data as 2 seperate steps.

No improvment in loading time.

Re: Filtering QV recrods in memory is slower than loading from file

flipside — Fri, 07 Dec 2012 09:06:37 GMT

I've just done some quick testing on this and if the QVD is written with the data sorted by the filtering field, I get a big improvement in load times by using an inner join rather than a where clause, so ...

LOAD

RandNumber
FROM [DataSorted.qvd] (qvd);
inner join
load 424372 as RandNumber autogenerate 1;

... gives a better result than ...

LOAD

RandNumber
FROM [DataSorted.qvd] (qvd)
where (RandNumber='424372');

If the data is unsorted there is no improvement.

flipside

Re: Filtering QV recrods in memory is slower than loading from file

Fri, 07 Dec 2012 09:13:10 GMT

Thanks.

But data is unsorted.