Laskentataulukko (spreadsheet), jossa on miljoona riviä ja tusina saraketta, on täysin tavallinen vienti (export) tietokannan raportointityöstä (reporting job). Avaa se tavalliseen tapaan, lataamalla koko työkirja (workbook) TXLSWorkbook-objektiin, ja prosessin on materialisoitava jokainen noista kahdestatoista miljoonasta solusta eläväksi objektiksi ennen kuin ensimmäinen rivisi liiketoimintalogiikkaa (business logic) ajetaan. Levyllä oleva tiedosto saattaa olla kuusikymmentä megatavua pakattua XML:ää. Objektipuu (object tree), johon se laajenee, on useita kertoja sen kokoinen, ja kaiken on oltava muistissa (resident) kerralla, koska malli on suunniteltu satunnaissaantiseksi (random-access). Raportille, jonka aiot lukea ylhäältä alas ja heittää pois, se on valtavasti muistia tuhlattuna rakenteeseen, jota et koskaan tarvinnut
Saman tiedoston läpi on olemassa toinen polku. Mallin (model) rakentamisen sijaan skannaat laskentataulukon (worksheet) XML:n vain eteenpäin, yksi solu kerrallaan, ja annat jokaisen solun virrata ohi sen jälkeen, kun olet katsonut sitä. Mitään ei kerry. Muistin käyttö pysyy lähes vakiona riippumatta siitä, onko taulukossa tuhat riviä vai kymmenen miljoonaa, koska lukijalla ei ole koskaan enempää tietoa kuin se osa, jota se parhaillaan jäsentää (parsing), sekä pari pientä hakutaulukkoa (lookup tables). Tätä HotXLS-suoralukija (direct reader) tekee, ja loppuosa tästä artikkelista käsittelee sitä, miksi se pysyy pienenä ja mitä se antaa sinulle vastineeksi
Miksi muistissa oleva malli (in-memory model) ei skaalaudu
XLSX-tiedosto on ECMA-376:n kuvailemien XML-osien ZIP-paketti (package). Jokainen laskentataulukko (worksheet) on oma osansa, xl/worksheets/sheetN.xml, ja sen sisällä jokainen rivi on <row>-elementti, joka sisältää <c> -soluelementtejä. Tavallinen latauspolku (load path) lukee tuon osan ja rakentaa osoitettavan (addressable) objektin jokaiselle solulle, jotta voit myöhemmin pyytää Cells[12345, 7] ja saada vastauksen vakioajassa (constant time). Satunnaissaanti (random access) on koko työkirjamallin pointti, ja juuri se tekee muokkaamisesta (editing), kaavojen laskemisesta (formula evaluation) ja tyylittelystä (styling) kätevää
Hinta on se, että satunnaissaanti vaatii kaiken olevan läsnä samanaikaisesti. Et voi indeksoida rakennetta, jonka olet rakentanut vain osittain. Joten täyden latauksen huippumuistinkulutus (peak memory) on funktio solujen määrästä, ja taulukossa (sheet), jossa on miljoonia täytettyjä soluja, tuo funktio päätyy johonkin, missä palvelusi ei halua olla, varsinkin jos useita tällaisia töitä (jobs) ajetaan kerralla jaetulla koneella (shared machine). Kun todellisuudessa tarvitsemasi saantimalli on peräkkäinen (sequential), satunnaissaannista maksaminen on maksamista kyvystä, jota et tule käyttämään
Vain eteenpäin menevä SAX-skannaus, joka ei rakenna puuta
Suoralukija (direct reader) avaa ZIP-paketin ja käy läpi (walks) jokaisen laskentataulukko-osan SAX-tyylisellä pull-jäsentäjällä (pull parser). SAX tässä tarkoittaa, että jäsentäjä raportoi jäsennystapahtumat (parse events) sitä mukaa, kun se kohtaa ne – aloituselementin (start element), tekstiosuuden (text run), lopetuselementin (end element) – ja siirtyy sitten eteenpäin. Se ei jätä solmupuuta (node tree) taakseen. Lukija pitää kirjaa nykyisestä rivistä ja sarakkeesta r-attribuuteista, kerää solun tyypin, tyyli-indeksin, arvon ja kaavatekstin (formula text) tapahtumien saapuessa, ja kun sulkeva </c>-tunniste (tag) nähdään, se lähettää yhden solun ja unohtaa sen. Seuraava solu käyttää uudelleen samat muutamat lokaalit muuttujat
Koska mitään ei säilytetä solujen välillä, muistijalanjälki (memory footprint) ei kasva solujen lukumäärän myötä. Se on ominaisuus, josta kannattaa pitää kiinni. Kahdensadan rivin taulukko (sheet) ja kahdenkymmenen miljoonan rivin taulukko maksavat lukijalle saman verran varattua muistia (resident memory), ja ero niiden välillä on vain se, kuinka kauan skannaus kestää. Luovut satunnaissaannista, mallin pääominaisuudesta, ja saat vastineeksi muistille katon, jota solujen lukumäärä ei voi lävistää
Mitä pysyy muistissa, ja miksi juuri nuo kaksi osaa
Skannaus ei ole täysin tilaton (stateless), ja poikkeukset ovat opettavaisia. Kaksi pientä taulukkoa on pidettävä muistissa (resident) koko keston ajan, koska solu yksinään ei sisällä tarpeeksi informaatiota tulkittavaksi ilman niitä
Ensimmäinen on jaettu merkkijonotaulukko (shared string table). SpreadsheetML:ssä tekstisolu ei tallenna omaa tekstiään. Se kantaa merkintää t="s" ja numeerista hyötykuormaa (payload), joka on indeksi tiedostoon xl/sharedStrings.xml, joka on yksi deduplikoitu luettelo työkirjan jokaisesta erillisestä merkkijonosta. Tämä on hyvä tilasäästö (space trade) tiedostoille, joissa samat otsikot (labels) toistuvat tuhansien rivien yli, mutta se tarkoittaa, että lukijan on ladattava tuo merkkijonotaulukko etukäteen (up front) ja pidettävä se muistissa, koska mikä tahansa solu missä tahansa taulukossa (sheet) voi viitata mihin tahansa siinä olevaan merkintään. Taulukon koko määräytyy erillisten merkkijonojen määrän, ei solujen määrän, mukaan, joten se pysyy maltillisena jopa valtavilla taulukoilla (sheets)
Toinen on numeromuoto-kartoitus (number-format mapping) styles-osasta. Numeerinen solu ja päivämääräsolu (date cell) ovat tiedonsiirtokaapelilla (on the wire) tavultaan samoja: molemmat ovat pelkkä luku, koska päivämäärä SpreadsheetML:ssä on vain sarjamuotoinen päivien lukumäärä (serial day count). Ainoa asia, joka erottaa ne toisistaan, on solun tyyli, joka osoittaa xl/styles.xml:n cellXfs-määrityksen kautta numeromuodon id:hen. Jotta päivämäärä voidaan raportoida päivämääränä (date) eikä raakana sarjanumerona (raw serial number), lukija lataa tuon tyyli-muoto-taulukon (style-to-format table) ja pitää sen muistissa. Kaikki muu tiedostossa – varsinainen soludata, joka muodostaa suurimman osan tavuista – virtaa (streams) ohi ilman, että sitä tallennetaan
Jokainen solu raportoi lajin (kind) ja arvon
Jokainen lähetetty (emitted) solu saapuu TXLSDirectCell-tietueena (record). Se kantaa taulukon indeksiä ja nimeä, 1-pohjaista riviä ja saraketta, semanttista lajia (Kind), arvoa (Value) muodossa Variant, kaavatekstiä (Formula) ilman johtavaa yhtäsuuruusmerkkiä ja raakaa tyyli-indeksiä (StyleIndex). Laji on jokin seuraavista: xdkNumber, xdkString, xdkBoolean, xdkDate tai xdkError, joten voit haarautua (branch) sen mukaan, mitä solu tarkoittaa, sen sijaan että johtaisit (re-deriving) sen uudelleen attribuuteista. Kaavasolu (formula cell) raportoi välimuistissa olevan tuloksensa (cached result) lajin kaavatekstin rinnalla, joten laskettu summa tulee läpi lukuna (number), joka kertoo myös sinulle, miten se on tuotettu
type
TReportScan = class
procedure OnCell(Sender: TObject; const Cell: TXLSDirectCell;
var Abort: Boolean);
end;
procedure TReportScan.OnCell(Sender: TObject; const Cell: TXLSDirectCell;
var Abort: Boolean);
begin
case Cell.Kind of
xdkString: AccumulateLabel(Cell.Row, Cell.Col, VarToStr(Cell.Value));
xdkNumber: AddToTotals(Cell.Col, Double(Cell.Value));
xdkDate: NoteWhen(Cell.Row, VarToDateTime(Cell.Value));
xdkBoolean: FlagRow(Cell.Row, Boolean(Cell.Value));
xdkError: LogBadCell(Cell.Row, Cell.Col, VarToStr(Cell.Value));
end;
end;Päivämäärän erottaminen luvusta
Päivämääräkysymys ansaitsee tarkemman katsauksen, koska siinä useimmat naiivit skannerit (scanners) menevät vikaan. Numeerisessa solussa ei ole päivämäärätyyppiä (date type). Solu, joka sisältää sarja-arvon 46000, voisi olla määrä, hinta tai 17. helmikuuta 2025, ja tiedosto kertoo sinulle kumpi se on, vain solun tyylin (style) kautta saavutetun numeromuoto-id:n (number-format id) avulla. ECMA-376 varaa lohkon sisäänrakennettuja formaatti-id:itä, joiden merkitys on kiinteä kaikilla vaatimustenmukaisilla tuottajilla (conforming producer), ja päivämääriä kantavat id:t sijaitsevat kahdella alueella: 14:stä 22:een vakiopäivämäärä- ja aikamuodoille, ja 45:stä 47:ään kuluneen ajan (elapsed-time) muodoille, kuten [h]:mm:ss. Kun DetectDates on päällä, mikä on oletus, lukija ratkaisee (resolves) jokaisen numeerisen solun tyylin sen formaatti-id:ksi, ja solu, jonka id osuu näille varatuille alueille, raportoidaan muodossa xdkDate niin, että sen Value on jo muunnettu Delphin TDateTime-tyypiksi. Myös mukautetut muodot (custom formats) tarkistetaan etsimällä muotokoodista päivämäärä- ja aikatunnisteita (tokens), mutta varatut alueet ovat luotettava selkäranka. Jos käännät DetectDates:n pois päältä, tyylitaulukkoa (styles table) ei edes ladata, jokainen numeerinen solu tulee läpi muodossa xdkNumber, ja skannaus on hieman (fractionally) kevyempi
Ohita taulukoita (sheets) ja keskeytä varhain (abort early)
Peräkkäisellä (sequential) skannauksella on hiljainen etu, jota satunnaissaanti (random access) ei voi päihittää: voit lopettaa. OnSheet-tapahtuma laukeaa (fires) ennen jokaisen laskentataulukon (worksheet) avaamista, ja se antaa sinulle kaksi kytkintä (switches). Aseta SkipSheet, ja tuota koko osaa ei koskaan jäsennetä, mikä on tapa, jolla skannaat vain ne taulukot (sheets), joista välität monilehtisessä (multi-sheet) työkirjassa, maksamatta loppujen lukemisesta. Aseta Abort, ja koko skannaus päättyy välittömästi. OnCell-tapahtuma kantaa omaa Abort-lippuaan, joten voit pysähtyä heti, kun olet löytänyt etsimäsi – tietyn rivin, vartiomiesarvon (sentinel value), otsikkolohkon (header block) lopun – lukematta jäljellä olevia miljoonia soluja. Vain eteenpäin menevässä skannauksessa (forward-only scan) keskeytys on todella ilmaista, koska ohittamasi työ on työtä, jota ei ollut vielä tapahtunut
procedure TReportScan.OnSheet(Sender: TObject; SheetIndex: Integer;
const SheetName: WideString; var SkipSheet: Boolean; var Abort: Boolean);
begin
// Scan only the "Data" sheet; leave the rest unread
SkipSheet := SheetName <> 'Data';
end;Solujen laskeminen ilman käsittelijää (handler)
Yksi hiljattainen hienosäätö (refinement) on syytä nostaa esiin, koska se muuttaa yleisen kysymyksen yhdeksi halvaksi kutsuksi (cheap call). Lukija laskee jokaisen täytetyn solun, jonka se ohittaa, ja se tekee tämän riippumatta siitä, onko OnCell-käsittelijä liitetty. Aiemmin, kun käsittelijää ei ollut asetettu, täytettyjen solujen määrä (populated-cell count) palautui nollana, koska laskeminen oli lähettämisen (emitting) sivuvaikutus. Nyt laskenta on riippumaton lähettämisestä. Tämä tarkoittaa, että voit kysyä yhden kysymyksen: kuinka monta täytettyä solua (populated cells) tämä työkirja todellisuudessa sisältää, ja saat vastauksen skannauksen hinnalla ilman ainuttakaan takaisinkutsua (callback). ReadFile ja ReadStream molemmat palauttavat kyseisen kokonaismäärän Int64-tyyppinä, ja sama luku on saatavilla myöhemmin CellCount-ominaisuutena. Palautusarvo -1 ilmoittaa (signals), että tiedostoa ei voitu avata tai se ei ole OOXML-paketti
var
Reader: TXLSDirectReader;
Populated: Int64;
begin
Reader := TXLSDirectReader.Create;
try
// No OnCell handler: a pure populated-cell census, still near-constant memory
Populated := Reader.ReadFile('quarterly_export.xlsx');
if Populated < 0 then
raise Exception.Create('Not a readable XLSX package')
else
Writeln(Format('%d populated cells (CellCount = %d)',
[Populated, Reader.CellCount]));
finally
Reader.Free;
end;
end;Täydessä skannauksessa liität käsittelijän (handler) ja kutsut ReadFile täsmälleen samalla tavalla. Kontrasti täyteen lataukseen (full load) on koko pointti: missä tiedoston quarterly_export.xlsx lataaminen työkirjaan laajentaisi jokaisen solun muistissa olevaksi (resident) objektiksi ja pitäisi koko joukon (the lot) tallessa, suoralukija pitää vain jaetut merkkijonot (shared strings) ja tyylitaulukon (style table), samalla kun kaksitoista miljoonaa solua virtaa OnCell-tapahtumasi läpi yksitellen (one at a time). Solukohtaisesti ajettu aritmetiikka ei jätä mitään jälkeensä, joten huippumuistinkulutus (peak memory) asettuu työkirjan erillisten merkkijonojen lukumäärän (distinct-string count) perusteella, ei sen rivien lukumäärän perusteella
Suoralukija (direct reader) on oikea työkalu, kun tehtävänä on lukea suuri työkirja (workbook) kerran ja poimia dataa tai tiivistää se. Kun tarvitset täyden mallin (full model) satunnaissaantia (random access), mutta haluat sen toimivan hyvin suurten tiedostojen kanssa, viritys (tuning) artikkelissa hotxls-large-workbook-performance-delphi.html, suuren työkirjan suorituskyky Delphissä, kattaa sen polun. Ja kun suunta on päinvastainen, suurten tulosteiden (output) tuottaminen niiden kuluttamisen (consuming) sijaan, hotxls-streaming-write-server-batch-jobs.html, suoratoistokirjoituspalvelimen eräajojen läpikäynti (streaming-write walkthrough for server batch jobs) soveltaa samaa vakiomuistikuria (constant-memory discipline) kirjoittamiseen. Kaikki kolme toimitetaan osana HotXLS Component -komponenttia Delphiä ja C++Builderia varten, niiden lukemis-, kirjoittamis-, kaava- ja muotoilu-API:den ohella, joita käsitellään muualla tässä blogissa