topic Loading from QVD takes long time with WHERE in QlikView

Loading from QVD takes long time with WHERE

david_ze — Wed, 16 Mar 2011 09:40:06 GMT

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

Loading from QVD takes long time with WHERE

Wed, 16 Mar 2011 09:48:54 GMT

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

Loading from QVD takes long time with WHERE

Wed, 16 Mar 2011 09:52:41 GMT

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.

Loading from QVD takes long time with WHERE

david_ze — Wed, 16 Mar 2011 10:00:03 GMT

Thanks,

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

Loading from QVD takes long time with WHERE

Wed, 16 Mar 2011 10:08:36 GMT

by using incramental load

you can use "where" only on the delta

Loading from QVD takes long time with WHERE

Wed, 16 Mar 2011 10:09:55 GMT

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

Loading from QVD takes long time with WHERE

Wed, 16 Mar 2011 10:20:26 GMT

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.