Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Frank_Kipry
Contributor II
Contributor II

Performance-Opt: ApplyMap instead of long WHERE-statement

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?

2 Solutions

Accepted Solutions
Frank_Kipry
Contributor II
Contributor II
Author

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.

View solution in original post

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

6 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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. 
 
Frank_Kipry
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Contributor II
Contributor II
Author

Cool, thank you, I will try it out!

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Where Not Exists(col);

-Rob