Qlik Sense Performance Tuning: QVD Optimization, Memory Management & Expression Speed

This is Article 26 of the Qlik Sense Data Modeling Course.

What Can You Learn About Performance Tuning in Qlik Sense?

After this guide, you will be able to:

1. Perform memory optimization with AutoNumber
2. Use optimized QVD loads (100x speedup!)
3. Write performance-optimized expressions

How to Measure Before Optimizing in Qlik Sense?

How to tune Qlik Sense performance?

What is QVD optimization in Qlik Sense?

For a comprehensive deep dive into QVD file formats, optimized vs. non-optimized loads, and storage strategies, see the dedicated QVD optimization guide.

What is the difference between optimized and non-optimized loads?

// NON-OPTIMIZED - Slow!
LOAD
    CustomerID,
    Upper(CustomerName) as CustomerName,  // Transformation!
    Country,
    Sales
FROM [DataCustomers.qvd] (qvd);

// OPTIMIZED - 100x faster!
LOAD
    CustomerID,
    CustomerName,  // No transformation
    Country,
    Sales
FROM [DataCustomers.qvd] (qvd);

Explanation:

• Optimized Load: Qlik loads data directly from QVD into memory
• Non-Optimized: Qlik must process each row individually
• Performance difference: 10-100x!

When Is a QVD Load Optimized?

What is the two-step load pattern?

// STEP 1: Optimized Load from QVD
TEMP_Customers:
LOAD
    CustomerID,
    CustomerName,
    Country
FROM [QVDCustomers.qvd] (qvd)
WHERE EXISTS(CustomerID);  // Stays optimized!

// STEP 2: Transformations on resident table
Customers:
LOAD
    CustomerID,
    Upper(CustomerName) as CustomerName,  // Now transform
    Country,
    If(Country = 'Germany', 'DE', Country) as CountryCode
RESIDENT TEMP_Customers;

DROP TABLE TEMP_Customers;

Explanation:

• Step 1: Optimized load – only relevant data, super fast
• Step 2: Transformations on smaller dataset
• Result: Best performance!

How Does WHERE EXISTS() Improve Performance?

// STEP 1: Load Facts (defines EXISTS pool)
Facts_Sales:
LOAD
    OrderID,
    CustomerID,  // Defines: Which CustomerIDs exist
    ProductID,
    Sales
FROM [QVDSales.qvd] (qvd);

// STEP 2: Load only relevant Customers
Dim_Customer:
LOAD
    CustomerID,
    CustomerName,
    Country
FROM [QVDCustomers.qvd] (qvd)
WHERE EXISTS(CustomerID);  // Only Customers with Sales!

Explanation:

• Without WHERE EXISTS: 1 million Customers loaded
• With WHERE EXISTS: 50,000 Customers (only those with Sales)
• Impact: 95% less data, much faster!

How can I optimize memory in Qlik Sense?

When memory issues become critical — causing crashes or “Out of Memory” errors — see our guide on diagnosing and resolving memory errors in Qlik Sense.

How do symbol tables affect Qlik Sense performance?

Qlik stores each unique value only once:

// CustomerID as String: "C00001", "C00002", ...
// Memory: 7 bytes x 1,000,000 rows = 7 MB

// CustomerID as Number: 1, 2, 3, ...
// Memory: 4 bytes x 1,000,000 rows = 4 MB
// Savings: 43%!

How to Optimize Key Generation Using AutoNumber()?

See the AutoNumber() function reference for full syntax details.

// WITHOUT AutoNumber - String Keys
LOAD
    CustomerID,      // "CUST-00001" = 11 bytes
    OrderID,         // "ORD-123456" = 11 bytes
    ProductID,       // "PROD-ABC-123" = 13 bytes
    Sales
FROM Sales.csv;

// WITH AutoNumber - Integer Keys
LOAD
    AutoNumber(CustomerID, 'Customer') as CustomerKey,  // 1,2,3,... = 4 bytes
    AutoNumber(OrderID, 'Order') as OrderKey,
    AutoNumber(ProductID, 'Product') as ProductKey,
    Sales
FROM Sales.csv;

Explanation:

• AutoNumber(): Converts strings to integers
• Second parameter: Context (don’t mix different IDs)
• Memory savings: 40-60% for large fact tables!

What are AutoNumber best practices?

//============================================
// TRANSFORM LAYER: Generate keys
//============================================

// Customers
TRF_Customers:
LOAD
    AutoNumber(CustomerID, 'Customer') as CustomerKey,
    CustomerID as CustomerID_Original,  // Keep original
    CustomerName,
    Country
FROM [QVDSTG_Customers.qvd] (qvd);

// Sales Fact
TRF_Sales:
LOAD
    AutoNumber(OrderID, 'Order') as OrderKey,
    AutoNumber(CustomerID, 'Customer') as CustomerKey,  // Same context!
    AutoNumber(ProductID, 'Product') as ProductKey,
    Sales,
    Quantity
FROM [QVDSTG_Sales.qvd] (qvd);

How to Optimize Expressions in Qlik Sense?

How does Set Analysis compare to IF() in Qlik Sense?

// SLOW: IF() iterates over all rows
Sum(If(Year = 2024, Sales))
Sum(If(Country = 'Germany' and Year = 2024, Sales))

// FAST: Set Analysis uses index
Sum({<Year={2024}>} Sales)
Sum({<Country={'Germany'}, Year={2024}>} Sales)

Performance difference: 5-10x faster!

How to Use Pre-Calculation in the Script?

// SLOW: Calculation in every chart
// Chart Expression:
Sum(Sales) / Sum(TOTAL Sales)  // TOTAL is expensive!

// FAST: Pre-calculate in the script
// Script:
LOAD
    OrderID,
    Sales,
    Sales / Sum(TOTAL Sales) as SalesPercent  // Calculate once
FROM Sales.csv;

// Chart Expression:
Sum(SalesPercent)  // Just a sum!

How to use flags instead of IF() in the frontend?

// Script: Prepare flags
LOAD
    OrderID,
    Sales,
    OrderDate,

    // Flags for frequent filters
    If(Year(OrderDate) = Year(Today()), 1, 0) as IsCurrentYear,
    If(Sales > 1000, 1, 0) as IsHighValue,
    If(Month(OrderDate) = Month(Today()), 1, 0) as IsCurrentMonth
FROM Sales.csv;

// Frontend SLOW:
Sum(If(Year(OrderDate) = Year(Today()), Sales))

// Frontend FAST:
Sum(Sales * IsCurrentYear)
// or with Set Analysis:
Sum({<IsCurrentYear={1}>} Sales)

Flag-based pre-calculation in the load script eliminates expensive runtime calculations entirely — moving the work from expression evaluation to data loading. This is one of the highest-impact optimizations you can make.

How to Use AGGR() Efficiently?

// SLOW: AGGR() in Chart
Sum(AGGR(
    If(Sum(Sales) > 10000, Sum(Sales), 0),
    Customer
))

// FAST: Pre-calculate in the script
// Script:
CustomerSales:
LOAD
    Customer,
    Sum(Sales) as CustomerSales
FROM Sales
GROUP BY Customer;

SalesWithFlag:
LOAD
    *,
    If(CustomerSales > 10000, CustomerSales, 0) as HighValueSales
RESIDENT CustomerSales;

// Chart:
Sum(HighValueSales)  // Simple!

How to optimize models in Qlik Sense?

How to use a star schema for optimal structure?

//============================================
// OPTIMAL: Star Schema
//============================================

// Facts in the center
Facts_Sales:
LOAD
    OrderID,
    CustomerKey,  // Link to Dim
    ProductKey,   // Link to Dim
    DateKey,      // Link to Dim
    Sales,
    Quantity
FROM [QVDTRF_Sales.qvd] (qvd);

// Dimensions around it
Dim_Customer:
LOAD
    CustomerKey,
    CustomerName,
    Country,
    Region
FROM [QVDTRF_Customer.qvd] (qvd);

Dim_Product:
LOAD
    ProductKey,
    ProductName,
    Category,
    Brand
FROM [QVDTRF_Product.qvd] (qvd);

Performance advantages:

• Faster aggregations
• Lower memory consumption
• Better query performance

How can I avoid synthetic keys in Qlik Sense?

// PROBLEM: Synthetic Keys
Orders:
LOAD OrderID, CustomerID, ProductID, Sales FROM Orders.csv;

OrderDetails:
LOAD OrderID, CustomerID, ProductID, Quantity FROM Details.csv;
// → Qlik creates $Syn1 (OrderID+CustomerID+ProductID)

// SOLUTION: Qualify or use unique keys
Orders:
LOAD
    OrderID,
    CustomerID,
    ProductID,
    Sales
FROM Orders.csv;

OrderDetails:
LOAD
    OrderID,              // Only shared key!
    Quantity
FROM Details.csv;

How to use Operations Monitor for profiling?

// Enable Profiling in Script
SET vProfiler = 1;

// Your code...

// Operations Monitor shows:
// - Execution Time per LOAD statement
// - Memory Usage per table
// - QVD Read Times

QMC → Operations Monitor:

• App Performance → Reload Time
• Session Performance → Chart Response Times
• Memory Usage per App

How to optimize Qlik Sense performance?

QVD Optimization:

• [ ] Use optimized loads (no transformations in FROM)
• [ ] WHERE EXISTS() for dimension filtering
• [ ] Two-step load pattern when needed

Memory Optimization:

• [ ] AutoNumber() for all keys in the transform layer
• [ ] Keep original IDs for debugging
• [ ] Check memory usage with Operations Monitor

Expression Optimization:

• [ ] Set Analysis instead of IF() where possible
• [ ] Flags in the script instead of calculations in the frontend
• [ ] Use AGGR() sparingly

Model Optimization:

• [ ] Star Schema instead of Snowflake
• [ ] Avoid Synthetic Keys
• [ ] Clearly separate Facts and Dimensions

How can I quickly tune Qlik Sense performance?

1. Activate optimized loads (5 min):

// Remove transformations from FROM clause
// Before:
LOAD *, Upper(Name) FROM Data.qvd (qvd);
// After:
LOAD * FROM Data.qvd (qvd);
NoConcatenate
LOAD *, Upper(Name) RESIDENT temp;

2. Add WHERE EXISTS() (10 min):

// For dimensions linking to facts
LOAD * FROM Customers.qvd (qvd)
WHERE EXISTS(CustomerID);

3. AutoNumber for keys (15 min):

// In the Transform Layer
LOAD AutoNumber(CustomerID, 'Customer') as CustomerKey, *
FROM STG_Customers.qvd (qvd);

How to measure Qlik Sense performance?

How to measure reload time in Qlik Sense?

LET vStartTime = Now(1);
TRACE ======================================;
TRACE RELOAD STARTED: $(vStartTime);
TRACE ======================================;

// ... Your load code ...

LET vEndTime = Now(1);
LET vDuration = Interval(vEndTime - vStartTime, 'hh:mm:ss');
TRACE ======================================;
TRACE RELOAD COMPLETED: $(vEndTime);
TRACE Duration: $(vDuration);
TRACE ======================================;

How do you measure table size in Qlik Sense?

SUB LogTableSize(pTableName)
    LET vRows = NoOfRows('$(pTableName)');
    LET vFields = NoOfFields('$(pTableName)');

    TRACE [TABLE SIZE] $(pTableName);
    TRACE   - Rows: $(vRows);
    TRACE   - Fields: $(vFields);
END SUB

CALL LogTableSize('Facts_Sales');
CALL LogTableSize('Dim_Customer');

How to troubleshoot Qlik Sense performance tuning?

// Check in Script Log:
// "4 fields found: X,Y,Z,... 12,345 lines fetched"
// If "Optimized" is NOT shown → Non-Optimized!

// Common causes:
// - Transformation in FROM (Upper, Date, etc.)
// - WHERE with function instead of literal
// - Preceding Load over QVD

// Check Table Memory in Document Properties
// If Keys (CustomerID, etc.) use a lot of memory → use AutoNumber

LOAD
    AutoNumber(CustomerID, 'Customer') as CustomerKey,
    // ... other fields
FROM Customers.qvd (qvd);

// Replace IF() with Set Analysis
// Before:
Sum(If(Year = 2024, Sales))

// After:
Sum({<Year={2024}>} Sales)

// Or: Flag in the script
// Script:
If(Year = 2024, 1, 0) as IsYear2024
// Chart:
Sum(Sales * IsYear2024)

How to tune Qlik Sense performance?

Phase 1: Measure (1 day)

1. Capture baseline: Reload time, memory, chart response
2. Analyze Operations Monitor
3. Identify bottlenecks

Phase 2: Quick Wins (1-2 days)

1. Activate optimized loads
2. Add WHERE EXISTS()
3. AutoNumber for keys

Phase 3: Deep Optimization (1 week)

1. Model review (Star Schema?)
2. Expression optimization
3. Implement incremental loads

Phase 4: Monitoring (ongoing)

1. Regularly check Operations Monitor
2. Track performance trends
3. Analyze on regression

What Are the Next Steps?

You can now systematically optimize Qlik apps! Next:

1. Deployment: Roll out optimized apps. Deployment shows how.

2. Three-Stage Architecture: Optimization points per layer. Combine with Three-Stage Architecture.