comparing "where exists" and "applymap"

[mehmet_caldak]

Hello Community,

i've a qvd has 49 M lines and i need to load it into a qwv file but not all lines just the lines filtered by a certain field. when i considered the possible methods to filter the data, i decided to test "where exists" and "applymap" techniques. firstly i tried  "where exists" and it took more than 17 min to load from qvd, then i tried "applymap", it was amazingly faster, it took just  less than 3 min. i can't understand, why such a big difference occur between these two methods ?

best regards.

[magavi_framsteg]

Hi mehmet.caldak.

I don't understand how you did it.

Normally, where exists and applymap are used in different scenarios.

Where exists is used to filter data, applymap is "normally" used to substitute data instead of joining it.

ie.

load

field1

,field2

from qvd

where exists(field2)

load

field1

,applymap('MyMap', field2, field2) as field2_description

from qvd

Kind regards

Magnus Åvitsland

BI Consultant

Framsteg Business Intelligence Corp.

[mehmet_caldak]

Hi Magnus Åvitsland,

first of all thank you for your reply.

let me to explain how i applied those scenarios.

in "where exists" i firstly loaded the table that contains the field i want to use for filter. then i pulled the data from my main qvd filtering with "where  exists(field1)"

table1.

load

     field1

from x.qvd where fieldX <> 'A';

table2:

load

field1

field2

field3

......

from main.qvd where exists(field1);

in the second scenario, "applymap", i created a mapping_load table than executed the main load filtered by applymap.

table1:

mapping load

field1

fieldX

from x.qvd;

table2:

Load

field1,

field2,

field3,

....

from main.qvd where applymap('table1',field1) <> 'A';

the scripts are just as like above. did i do something wrong ?

best regards.

[alexmosq]

Clever! Definitely gonna try this.

[preminqlik]

new idea ..

[yassinemhadhbi]

i tried it too , but ther opposite had happened to me.

Where exists is much fater than the where applymap  ...

[jonathandienst]

This is clearly a case of YMMV - it depends on the cardinality of the exists field among others. So test both in your scenario.