One of the best ways to learn various scripting and the script optimization concepts is to use the test script. Both QlikView and Qlik Sense script editors provide a way for you to generate test script on the fly.

QlikView:

You can use Insert — Test Script option to generate script and then modify script to generate a data set as large as you wish.

The script creates a transaction and dimension tables.  You can change # of rows by simply changing autogenerate statement with the desired # of rows.  For example, if you want to create data set with 10 million rows then change the statement as follows:

Load
Rand() as Rand1,
IterNo() as TransLineID,
RecNo() as TransID
Autogenerate 10000000

;

Qlik Sense:

Use CTRL + 00(two zeros) to generate the test script.

Set the stage

I wanted to test script performance using various approaches so I created a QVD of 20 million records using the test script first.

Let’s start with the Ugly first.

The Ugly:

Let’s assume that I needed to perform calculation using fields TransID and Num so my natural approach  — commonly used by most novice developers — is to use resident load and the join the table back to the Transaction table as show in the depiction.

Resident load is resource intensive so it takes 30 seconds.  Not bad, right?  Wrong!

Although 30 seconds may not seem too bad, imagine that you are running same script against 200 million records instead of 20 million.

The Bad:

Now you are thinking how to get rid of that ugly looking resident load? And your brain came up with a brilliant idea — let’s perform calculation in the transaction load statement.

You promptly modified your script and anxiously waited for 15 seconds for the reload to complete. You are exuberant as you just improved reload time by 50%. It can’t get any better right?

Wrong again!

The Good:

You just stumbled upon a brilliant HIC article, Symbol tables and bit-stuffed pointers, and a new idea dawned on you.  Why not use knowledge of the symbol table at work? You started looking at Qlik help section and there you found the function that can make you shine — FieldValue() function.

FieldValue function — as name suggests — works on a field. And, field always contains unique values so instead of scanning millions of table records, you now are going to use FieldValue() function for the calculation using a preceding load statement.

Your brilliance finally paid off once you reloaded your script since it took only 6 seconds now.

WOOHOO!

The Ugly — 30 seconds;  The Bad — 15 seconds;  The Good — 6 seconds.  (20 million records).