Optimized Load

I stora applikationer är det viktigt att optimera inläsning av data för att korta ner tiden. En av de saker man kan använda för detta är optimized load som läser in QVD-fil på ett mycket effektivt sätt.



Det är mycket vanligt att vi mellanlagrar information från olika datakällor i QVD-filer. En QVD-fil kan innehålla en tabell med flera kolumner och så många rader som behövs. Syftet med QVD-filen är dels att bespara datakällan från multipla läsningar av samma information från flera olika appar samt att kunna applicera transformationslogik i flera steg.


När vi sedan läser data från en QVD-fil till en app kan den läsas på två sätt - optimized och non-optimized load. Optimized load är avsevärt snabbare, men ställer stora krav på hur läsningen görs. För att en load ska bli optimized kan vi endast:

  • Filtrera data med en enda exists()

  • Döpa om kolumner

  • Läsa en delmängd av kolumnerna

  • Keep/Join mot annan tabell

  • Läsa ett fält flera gånger

  • Läsa data distinct

Vi kan alltså INTE:

  • Räkna ut en ny kolumn (t.ex. ColumnA+1 as Column3)

  • Where-satser utöver en enda exists()

  • Använda applymap()

Som exempel här ska vi testa att läsa en QVD-fil med 131 miljoner rader & 13 kolumner (drygt 4GB data) för taxiresor i New York. Versionen vi testar att läsa in data i är QlikView Desktop April 2020 SR1.


För att läsa in data som optimized load använder vi:

Data:
LOAD VendorID, 
     tpep_pickup_datetime, 
     tpep_dropoff_datetime, 
     passenger_count, 
     trip_distance, 
     pickup_longitude, 
     pickup_latitude, 
     dropoff_longitude, 
     dropoff_latitude, 
     payment_type, 
     fare_amount, 
     tip_amount, 
     trip_time
FROM [F:\Temp\NY_ShortFull.qvd] (qvd);

Man kan se att inläsningen är optmized eftersom det framgår i loggen för inläsningen:

Data << NY_ShortFull (row-based qvd optimized) 131,165,043 Lines fetched

För att tvinga att läsningen inte blir optimitzed utan att lägga på någon större komplexitet (som i sig tynger ner prestandan) lägger vi till en enkel where sats:

Data:
LOAD VendorID, 
     tpep_pickup_datetime, 
     tpep_dropoff_datetime, 
     passenger_count, 
     trip_distance, 
     pickup_longitude, 
     pickup_latitude, 
     dropoff_longitude, 
     dropoff_latitude, 
     payment_type, 
     fare_amount, 
     tip_amount, 
     trip_time
FROM [F:\Temp\NY_ShortFull.qvd] (qvd) where 1=1;

För non-optimized load framgår inget speciellt i loggen:

Data << NY_ShortFull 131,165,043 Lines fetched

Så hur mycket skiljer det i tid för inläsningen? En optmized load tog på min stationära klientdator 27 sekunder och för non-optimized tog det på samma maskin 62 sekunder - ungefär dubbelt så lång tid.


Filtrera ut värden

Så hur gör du om du vill filtrera ut vissa värden? Anta att vi vill läsa ut data från QVD-filen ovan men endast ta med VendorID=1 eller 3. Vi kan inte lägga till en where-sats enligt nedan då det skulle göra inläsningen non-optizimed:

<LOAD-sats>
where VendorID=1 or VendorID=3

Dock får vi lov att använda en exists()-funktion. Exists() jämför värden med redan inlästa värden i ett fält. Vi kan använda detta genom att först läsa in VendorID 1 och 2 i ett fält och sedan lägga till en where-sats med exists().

Load * INLINE [
    VendorID
    1
    3
];

<LOAD-sats>
where exists(VendorID);

I det första non-optimized-fallet tar utläsningen av 61 miljoner rader 61 sekunder och i det andra fallet med optimized load 20 sekunder.


Sparade värden

Ibland sparar vi med en viss frekvens ned data i snapshots som sedan sammanfogas i en applikation för att se hur något förändras över tid. Det kan till exempel handla om Produkter i Arbete (PIA/WIP), lagersaldon och liknande.


Om vi utgår från snapshots av lagersaldon som ett exempel kan vi fundera på hur man bäst beräknar en värdering av lagret. Ett vanligt enkelt sätt att beräkna ett lagervärde är att ta saldo multiplicerat med standardkostnaden av respektive artikel.

Lagersaldo*Standardkostnad as Lagervärde

Tänk nu på vad vi lärt oss ovan. Var ska vi göra beräkningen av Lagervärde? Ska vi göra det i QVD-skaparen som sparar ett snapshot varje månad eller i applikationen som läser in alla snapshots? Jämför nedan:


Lagervärde beräknas när snapshots sparas:

QVD-skapare:

Let vToday = text(date(today(),'YYYYMMDD');
Lager:
    ArtikelNr,
    Lagersaldo,
    Standardpris,
    Lagersaldo*Standardpris as Lagervärde
from Lager.qvd (qvd);
store Lager into Snapshot_Lager_$(vToday).qvd (qvd);

Slutapp:

Load * resident Snapshot_Lager*.qvd (qvd);

Lagervärde beräknas när snapshots läses in:

QVD-skapare:

Let vToday = text(date(today(),'YYYYMMDD');
Lager:
    ArtikelNr,
    Lagersaldo,
    Standardpris
from Lager.qvd (qvd);
store Lager into Snapshot_Lager_$(vToday).qvd (qvd);

Slutapp:

Load
    *,
    Lagersaldo*Standardpris as Lagervärde
resident Snapshot_Lager*.qvd (qvd);

Vilket av de två fallen ovan kommer att gå snabbare? Rätt svar är när Lagervärde sparas ned i snapshot. Eftersom fältet redan finns i QVD:n kan filen läsas optimized. I det andra fallet gör vi en beräkning vid inläsningen, vilket gör att läsningen blir non-optimized.


Ska allt vara optimized?

Så betyder det här att best-practice är att allt ska läsas in optimized? Nej - bedöm vad du har att tjäna på att optimera inläsningen. Det är troligen onödigt att lägga några timmars arbete på att optimera något där du kan vinna en minut - om du inte ska ladda om appen mycket frekvent.


Skriven av: Morgan Kejerhag

Morgan Kejerhag har arbetat med Qlik-plattformen sedan 2005 och är en av Sveriges mest erfarna konsulter. Under åren har Morgan arbetat med flertalet multinationella bolag där han lett arbetet i att bygga upp stora Qlik-miljöer såväl som små kunder. LinkedIn Kontaktuppgifter