How to use QV function WHERE exists for qvd optimized load

[#1]

Hi everybody,

I'm not so sure if you
already know and use following trick. The first time I was disappointed with
loading performance from qvd using where condition.

Example:

load FIELD from My.qvd
(qvd)

WHERE
FIELD2='Value_for_condition';

Loading from big files took
really long and condition caused not optimized load.

I tried following:

1) Define temporary table
using load * inline [];

2) using this temp table in
condition as where exists ();

Example:

TEMP:

load * inline

[FIELD2

Value_for_or_condition

Value_for_or_condition2

];

TABLE:

load FIELD, FIELD2 from
My.qvd (qvd) WHERE EXISTS ([FIELD2]);

drop table TEMP;

*******

Well, this has certain
issues.

- Field used in a condition
has to be selected as well, even if it is not needed (otherwise not qvd optimized)

- two loads load *, x&z
as y; load * from (qvd); -> is slow, not a qvd optimized

- two condition fileds are
not possible with where exists -> FIELD2='11' AND FIELD3='22' -> causes
not optimized qvd load

How do you handle more
combined where conditions or transformation while loading from qvd file? (where
data in qvd are not in the needed format for any reason)

Regards

Stan

[sebastiandperei]

Thanks for the idea! I didn't know this way to do an optimized load.

So, if I understood, you are asking how you could do a combination of conditions. I think you could do something like:

suposing that we will loading like:

Load *

From table.qvd

Where (a=100 and b=78) or (b=500 and a=69);

I guess you could do:

TEMP:

load * inline

[a, b

100, 78

69,   500

];

Left join

Load Distinct a, b, a&'_'&b as a_b Resident TEMP;

Table:

Load *

From table.qvd(qvd)

Where exists (a&'_'&b, a_b);

I dont know if this will be optimized, but you can try at less!!

[#3]

Hi , If you concatenate in the where exists function , it changed qvd load as un-optimized load. In this case in the QVD loader we need to create the composite keys.

-------- QVD Loader -------

TABLENAME:

LOAD * , A&'-'&B AS KEY;

SELECT * FROM TABLENAME;

STORE TABLENAME into TABLENAME.qvd (qvd) ;

------- MAIN QVW --------

TEMP:

LOAD A & '-' & B AS KEY INLINE [

A , B

10, 100

32, 165

45, 90

];

LOAD * TABLENAME.qvd (qvd)

Where EXISTS(KEY);

DROP Table TEMP ;

This Load keeps Optimized load. If you are not doing any incremental load , use Autonumber functions for composite keys.

[#4]

Thank you for the reply.

I was thinking about the same composite key approach. However you can never know if there won't be a similar need with another concatenated key. Then there has to be another A&C field.

In a selection, all possible combinations should be given, not just where Field (A,B,C) AND Field2(D,E,F). You should know then combinations like AD, AE, AF, BD, BE, BF ....

But I will keep it on my mind.

Regards

Stan