topic MATCH function in a LOAD in QlikView

MATCH function in a LOAD

Tue, 27 May 2014 16:01:55 GMT

If you use a MATCH function in a LOAD, will this create an unoptimized LOAD? My presumption is yes, but wanted to check.

Re: MATCH function in a LOAD

Anonymous — Tue, 27 May 2014 16:08:26 GMT

Alexander

Yup, it will unoptimise the load.

Re: MATCH function in a LOAD

israrkhan — Tue, 27 May 2014 16:32:01 GMT

According to my knowledge, unoptimize load mean, the Script which has a WHERE CLAUSE.

even it is very simple like where ID =10.

in this case for every row, the where clause will be checked, so it will take lot of time in comparison.

correct me ,if i am wrong...

Re: MATCH function in a LOAD

Tue, 27 May 2014 17:54:33 GMT

Hi, Alexander.

MATCH on large data sets faster OR.

My test:

test_data:

LOAD Floor(Rand()*100) as ID

AutoGenerate(100000000); // Attention!!!! 100 millions rows will be generated

LET vStart = Num(Now());

test1:

LOAD ID as test1ID

Resident test_data

Where Match(ID,1,2,3,4,5,6,7,8,9,10);

LET vEnd = Num(Now())-Num($(vStart));

TRACE $(vEnd);

DROP Table test1;

LET vStart = Num(Now());

test2:

LOAD ID as test2ID

Resident test_data

Where ID=1 or ID=2 or ID=3 or ID=4 or ID=5 or ID=6 or ID=7 or ID=8 or ID=9 or ID=10;

LET vEnd = Num(Now())-Num($(vStart));

TRACE $(vEnd);

DROP Table test2;

Re: MATCH function in a LOAD

Joseph_Musekura — Tue, 27 May 2014 17:56:55 GMT

Hi,

I think that any condition, expression used in load statement that would require a QVD (for example) to be unpacked in order to evaluate the condition, expression etc.. will lead to unoptimized mode.

Regards,