Filtering QV recrods in memory is slower than loading from file

[#1]

I have the below code which will

1. Read a qvd file,
2. Filter the data and
3. 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?

[Gysbert_Wassenaar]

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];


[#3]

Thanks for the reply.

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

No improvment in loading time.

[flipside]

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

[#5]

Thanks.

But data is unsorted.