Performance-Opt: ApplyMap instead of long WHERE-statement

[Frank_Kipry]

Hey,

since I work now since 2 months with QlikView and since my company has way to many QlikView-Reports and facing a problem of overall-run time, I want to focus as much as possible on runtime optimization as possible.

But, since also different users are using the same code, I also want to make the code 'pretty'.

So, here my general question, what you guys think:

classic code: 

          Where col <> 'test' or col <> 'dummy' or col <> 'try';

my idea:

          Where IsNull( ApplyMap(col)  )                     

[I would of course put a map beforehand, where I only map 'test', 'dummy' and 'try' with NULL()]

What do you guys think, would it greatly impact the run time? Would you think the code would be easier to read?

[rwunderlich]

I think the easiest to read and write may be:

Where Match(col, 'test', 'dummy', 'try')

I don't guess using a map would be significantly faster or slower, but give it a test if you are curious. 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[Frank_Kipry]

Sorry for the late answer, we had a holiday yesterday, so I didn't look up the post.

Yeah, I was also thinking about match, but I was thinking of storing all parameters central on a tab...

But I just try it out, I was just wondering if someone else already had experience with it.

And thank you!!

[Frank_Kipry · Accepted solution]

Ok, I found a nice history-table with 32 mio entries, and set the filter for 3 criteria (result: 170.000 entries).

with match it took 41sec, with mapping 48sec.

So, definitely not worth it.

[rwunderlich · Accepted solution]

If you are loading from QVD, an even better and faster approach is exists()

TempCol:
LOAD * INLINE [
col
test
dummy
try
];

Mydata:
LOAD *
FROM myqvd.qvd (qvd)
Where Not Exists(col);

DROP Table TempCol;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[Frank_Kipry]

Cool, thank you, I will try it out!

[rwunderlich]

Wooops. I just noticed that you were testing for not equals.  The where in. my example should include Not

Where Not Exists(col);

-Rob