100 Million Rows, Zero Drama: Practical Load Optimisation
Speed matters. When your data updates in a blink, you barely notice — but throw in hundreds of millions of rows, and suddenly every refresh feels like forever. That’s when smart optimised loading saves the day.
Let’s take a simple example: a QVD file consisting of 10 fields and 100,000 rows. It takes 30-37 seconds to load.
Optimised filterring with Where Exists
Task : Filter the table where Dim1 = ‘C’. The essential thing is to apply WHERE EXISTS statement.
Problem: The Loading Time is increasing x2
Solution: Creating a Inline table with filtering parameters for Dim1 and Where Exists Dim 1 Statement in the loading Table
WHERE EXISTS with 2 parameters
Problem: The optimised load would be broken if create a filtering inline table with the Field Fltr and apply WHERE EXISTS with 2 parameters (Fltr,Dim1) in the main dataset:
Solution: Both fields should exist in the main Data Set. Create extra field in the main data set and apply WHERE EXISTS with the 2 parameters (Fltr,Dim1):
HINT: Residential Load
Multiple Filters : Do one filtering at a time.Apply residential load on firstly filtered Data Set with the new filtering condition:
ApplyMap: Load the Table with the fields for Mapping Table first and based on residential load create a Mapping Table:
Data Transformation: Load the whole Table first and based on residential load apply transformations:
Applying Date Filter
Problem: The Loading time increasing when applying Date Filter like Where Year (Date) = 2025
Solution: Split the dataset. Instead of loading the entire dataset every time, split it into smaller parts during the extraction.
HINT: Dynamic Range Loading
Example: Loading only the last 12 months of data. Build a simple calendar that calculates the date range dynamically, using an offset variable to define the number of months to include.
