Mapping Load vs. Apply Map vs. If load timing test

I was discussing with QlikTech which was the fastest method of adding a small relational table to a table. Basicly you have 3 options: Table join, Mapping load and If(). The table join method is discarded on forehand because it will run though every row in the loaded table. The mapping load method has two methods either you can in your load syntax use ApplyMap(Field,Map1) or afterwards do a Map Field Using Map1. The last method is doing a simply If inside your load. According to QlikTech the two Mapping methods should be the fastest. But since the one is done inside your load script, the data will be loaded slower. QlikView can load data in fast and super fast mode. And when using If or ApplyMap you cannot use super fast and it will use to fast instead.

So I decided to test out what is the fastet. What I did was to create 3 load statements against a qvd file with 21.662.059 rows.

My timing script looks like this:

X_ReloadTime:
LOAD
 now() as ReloadTime,
 OSUser() as ReloadUser
AUTOGENERATE (1);

// set load time
LET StartTime =  (peek('ReloadTime',-1,'X_ReloadTime'));
DROP TABLE X_ReloadTime;

StatusMap:
mapping load * inline [
Status, Value
0,'Undefined'
1,'Checked in'
2,'Not checked in'
3,'Released' ] ;

// Make sure all names er unique
Qualify *;

X_ReloadTime1:
LOAD
 now() as ReloadTime1
AUTOGENERATE (1);
LET StartTimeService1 =  (peek('X_ReloadTime1.ReloadTime1',-1,'X_ReloadTime1'));
DROP TABLE X_ReloadTime1;

Tab1:
LOAD 
 KEY_ONE,
 If(STATUS = 0,'Undefined',
  If(STATUS= 1,'Checked in',
   If(STATUS = 2,'Not checked in',
    If(STATUS = 3,'Released')))) 
 as STATUS,
 KEY_TWO
FROM ..\Data\Tab.qvd (qvd);

X_ReloadTime2:
LOAD
 now() as ReloadTime2
AUTOGENERATE (1);
LET StartTimeService2 =  (peek('X_ReloadTime2.ReloadTime2',-1,'X_ReloadTime2'));
DROP TABLE X_ReloadTime2;

Tab2:
LOAD 
 KEY_ONE,
 ApplyMap('StatusMap',STATUS) 
 as STATUS,
 KEY_TWO
FROM ..\Data\Tab.qvd (qvd);

X_ReloadTime3:
LOAD
 now() as ReloadTime3
AUTOGENERATE (1);
LET StartTimeService3 =  (peek('X_ReloadTime3.ReloadTime3',-1,'X_ReloadTime3'));
DROP TABLE X_ReloadTime3;

Map Tab3.STATUS USING StatusMap;

Tab3:
LOAD 
 KEY_ONE,
 STATUS,
 KEY_TWO
FROM ..\Data\Tab.qvd (qvd) WHERE 1=1;

X_ReloadTime4:
LOAD
 now() as ReloadTime4
AUTOGENERATE (1);
LET StartTimeService4 =  (peek('X_ReloadTime4.ReloadTime4',-1,'X_ReloadTime4'));
DROP TABLE X_ReloadTime4;

X_LoadTime:
LOAD
 interval(now() - '$(StartTime)','hh:mm:ss') as LoadTime,
 interval('$(StartTimeService2)'-'$(StartTimeService1)','hh:mm:ss') as LoadTimeService1,
 interval('$(StartTimeService3)'-'$(StartTimeService2)','hh:mm:ss') as LoadTimeService2,
 interval('$(StartTimeService4)'-'$(StartTimeService3)','hh:mm:ss') as LoadTimeService3  
AUTOGENERATE (1);

Notice the bold WHERE 1=1, this is because that during this test I found out that Map Using does not work when running in superfast mode. I've written QT to discouss if its a bug or a silly WAD (work as designed).

The timings gave this result:          

So the big one is that If() is faster than ApplyMap contrary to what QlikView states. But when using Map Field Using Map1; you're supposed to be able to load in superfast mode, but if you do (try removing WHERE 1=1) then you're punished, and you're statement gets beating by the If()!

QlikTech, I'm sorry, loading data using mapping is slower than simple If() statements! It still more elegant, but come on, we trust what you say, and we tend to work that way.

Regards Seebach