Loading from QVD takes long time with WHERE

[david_ze]

Hi all,

I'm loading data from QVD file (300M recs) and using WHERE in the load statment:

Params:

load

event_id as id,

"param_name",

"param_value"

FROM event_params.qvd

(qvd) WHERE event_id>=$(vMin_id) and event_id<=$(vMax_id);

can someone advice me why it take so long to load the data? is it something I should change in the load script?

Will appreciate your help.

Thanks,

David

[#2]

David,

The 'where' clause on a QVD affects optimisation.

Try loading the QVD completely (so that it is optimised) and then apply the 'where' to the resultant table.

Regards,

Gordon

[#3]

There is one exception, and that is the EXISTS function. You can have an optimized load with a WHERE EXISTS or WHERE NOT EXISTS clause.

[david_ze]

Thanks,

can you reply me with the syntax I should use in my script?

[#5]

by using incramental load

you can use "where" only on the delta

[#6]

Try this:

tParams:

load

event_id,

"param_name",

"param_value"

FROM event_params.qvd (qvd);

Params:

load

event_id as id,

"param_name",

"param_value"

resident tParams

WHERE event_id>=$(vMin_id) and event_id<=$(vMax_id);

drop table tParams;

Note that as event_id is renames as id in table Params then the data will not be concatenated with table tParams.

Regards,

Gordon

[#7]

Assuming you already have the IDs that you need to load in a field [id], you can modify your WHERE clause to:

WHERE EXISTS(id,event_id)

...this way you don't need the variables and you keep the optimized load.