topic Re: Performance-Opt: ApplyMap instead of long WHERE-statement in QlikView

Performance-Opt: ApplyMap instead of long WHERE-statement

Frank_Kipry — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Performance-Opt: ApplyMap instead of long WHERE-statement

rwunderlich — Fri, 29 May 2020 18:19:09 GMT

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

Re: Performance-Opt: ApplyMap instead of long WHERE-statement

Frank_Kipry — Tue, 02 Jun 2020 06:16:41 GMT

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!!

Re: Performance-Opt: ApplyMap instead of long WHERE-statement

Frank_Kipry — Tue, 02 Jun 2020 08:34:04 GMT

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.

Re: Performance-Opt: ApplyMap instead of long WHERE-statement

rwunderlich — Thu, 04 Jun 2020 16:52:52 GMT

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

Re: Performance-Opt: ApplyMap instead of long WHERE-statement

Frank_Kipry — Thu, 04 Jun 2020 05:39:48 GMT

Cool, thank you, I will try it out!

Re: Performance-Opt: ApplyMap instead of long WHERE-statement

rwunderlich — Thu, 04 Jun 2020 16:52:31 GMT

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

Where Not Exists(col);

-Rob