Tabuľka s miliónom riadkov a dvanástimi stĺpcami je úplne bežným exportom z databázovej reportingovej úlohy. Otvorte ju bežným spôsobom – načítaním celého zošita do TXLSWorkbook – a proces musí materializovať každú z tých dvanástich miliónov buniek ako živý objekt ešte predtým, než sa spustí váš prvý riadok obchodnej logiky. Súbor na disku môže mať šesťdesiat megabajtov skomprimovaného XML. Strom objektov, na ktorý sa rozbalí, je niekoľkonásobne väčší a musí byť prítomný naraz, pretože model je svojou konštrukciou určený pre náhodný prístup. Pre report, ktorý chcete čítať od začiatku do konca a potom zahodiť, je to veľa pamäte spotrebovanej na štruktúru, ktorú ste vôbec nepotrebovali
Existuje druhá cesta cez ten istý súbor. Namiesto budovania modelu prehľadávate XML pracovného listu iba dopredu, jednu bunku po druhej, a každú bunku necháte preplynúť po tom, čo ste sa na ňu pozreli. Nič sa nehromadí. Pamäť zostáva takmer konštantná bez ohľadu na to, či má list tisíc alebo desať miliónov riadkov, pretože čítač nikdy nedrží viac ako práve spracovávanú časť plus niekoľko malých vyhľadávacích tabuliek. Toto robí priamy čítač HotXLS a zvyšok tohto článku vysvetľuje, prečo zostáva malý a čo vám za to dáva
Prečo sa in-memory model neškáluje
Súbor XLSX je ZIP balík XML súčastí popísaných normou ECMA-376. Každý pracovný list je vlastnou súčasťou, xl/worksheets/sheetN.xml, a vnútri neho je každý riadok prvkom <row> obsahujúcim prvky buniek <c>. Štandardná cesta načítania túto súčasť prečíta a pre každú bunku vytvorí adresovateľný objekt, aby ste neskôr mohli požiadať o Cells[12345, 7] a dostať odpoveď v konštantnom čase. Náhodný prístup je celým zmyslom modelu zošita a práve to robí úpravy, vyhodnocovanie vzorcov a formátovanie pohodlnými
Cena za to je, že náhodný prístup vyžaduje, aby bolo všetko prítomné súčasne. Nemôžete indexovať do štruktúry, ktorú ste len čiastočne zostavili. Preto je maximálna pamäť pri plnom načítaní funkciou počtu buniek, a na liste s miliónmi obsadených buniek táto funkcia pristane na mieste, kde vaša služba nechce byť, obzvlášť ak niekoľko takýchto úloh beží naraz na zdieľanom stroji. Keď je vzorec prístupu, ktorý skutočne potrebujete, sekvenčný, platíte za náhodný prístup ako za schopnosť, ktorú nevyužijete
Dopredné SAX skenovanie bez budovania stromu
Priamy čítač otvorí ZIP balík a prechádza každou súčasťou pracovného listu pomocou ťahového parsera v štýle SAX. SAX tu znamená, že parser hlási udalosti parsovania pri ich výskyte – začiatočný prvok, textový beh, koncový prvok – a potom pokračuje ďalej. Za sebou neudržiava žiadny strom uzlov. Čítač sleduje aktuálny riadok a stĺpec z atribútov r, zbiera typ bunky, index štýlu, hodnotu a text vzorca pri príchode udalostí, a keď je zaznamenaný záverečný tag </c>, vyšle jednu bunku a zabudne na ňu. Nasledujúca bunka znovu použije rovnakú hŕstku lokálnych premenných
Keďže medzi bunkami nič nezostáva, pamäťová stopa nenarastá s počtom buniek. To je vlastnosť, ktorú stojí za to si udržať. List s dvesto riadkami a list s dvadsiatimi miliónmi riadkov stoja čítača rovnakú rezidenčnú pamäť a rozdiel medzi nimi je iba v tom, ako dlho skenovanie trvá. Vzdáte sa náhodného prístupu, hlavnej vlastnosti modelu, a na oplátku dostanete strop pamäte, cez ktorý sa počet buniek nedostane
Čo zostáva rezidentné a prečo práve tieto dve časti
Skenovanie nie je úplne bezstavové a výnimky sú poučné. Dve malé tabuľky musia byť počas celej doby udržiavané v pamäti, pretože samotná bunka nenesie dostatok informácií na interpretáciu bez nich
Prvá je tabuľka zdieľaných reťazcov. V SpreadsheetML textová bunka neukladá vlastný text. Nesie t="s" a číselné zaťaženie, ktoré je indexom do xl/sharedStrings.xml – jedného deduplikovaného zoznamu každého odlišného reťazca v zošite. Toto je dobrý priestorový kompromis pre súbory, kde sa rovnaké popisky opakujú v tisíckach riadkov, ale znamená to, že čítač musí nahrať túto tabuľku reťazcov vopred a udržiavať ju rezidentnú, pretože akákoľvek bunka kdekoľvek v ktoromkoľvek liste môže odkazovať na akýkoľvek záznam v nej. Tabuľka je dimenzovaná podľa počtu odlišných reťazcov, nie podľa počtu buniek, takže zostáva skromná aj na obrovských listoch
Druhá je mapovanie číselných formátov zo súčasti štýlov. Číselná bunka a dátumová bunka sú na prenose bajt po bajtom rovnaké: obe sú jednoduché čísla, pretože dátum v SpreadsheetML je iba sériový počet dní. Jediná vec, ktorá ich odlišuje, je štýl bunky, ktorý cez cellXfs v xl/styles.xml ukazuje na ID číselného formátu. Aby bol dátum hlásený ako dátum a nie ako surové sériové číslo, čítač načíta túto tabuľku štýl-formát a udržiava ju rezidentnú. Všetko ostatné v súbore – skutočné dáta buniek, ktoré tvoria väčšinu bajtov – streamuje bez ukladania
Každá bunka hlási druh a hodnotu
Každá vyslaná bunka prichádza ako záznam TXLSDirectCell. Nesie index a názov listu, 1-bazovaný riadok a stĺpec, sémantický Kind, Value ako Variant, text Formula bez úvodného znamienka rovnosti a surový StyleIndex. Druh je jeden z xdkNumber, xdkString, xdkBoolean, xdkDate alebo xdkError, takže môžete vetvovať podľa toho, čo bunka znamená, namiesto opätovného odvodzovanie z atribútov. Bunka so vzorcom hlási druh svojho uloženého výsledku spolu s textom vzorca, takže vypočítaný súčet prichádza ako číslo, ktoré vám tiež hovorí, ako bol vyprodukovaný
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;Rozlišovanie dátumu od čísla
Otázka dátumu si zaslúži bližší pohľad, pretože práve tu väčšina naivných skenerov zlyhá. Na číselnej bunke neexistuje dátumový typ. Bunka obsahujúca sériovú hodnotu 46000 môže byť množstvo, cena alebo 17. február 2025, a súbor vám hovorí, ktoré z toho je, iba cez ID číselného formátu dosiahnuté cez štýl bunky. ECMA-376 rezervuje blok vstavaných ID formátov, ktorých význam je pevne daný naprieč každým konformným producentom, a ID nesúce dátum sa nachádzajú v dvoch rozsahoch: 14 až 22 pre štandardné formáty dátumu a času, a 45 až 47 pre formáty uplynulého času ako [h]:mm:ss. Keď je DetectDates zapnutý, čo je predvolene, čítač preloží štýl každej číselnej bunky na jej ID formátu, a bunka, ktorej ID spadá do týchto rezervovaných rozsahov, je hlásená ako xdkDate s hodnotou Value už prevedenou na Delphi TDateTime. Kontrolujú sa aj vlastné formáty, skúmaním kódu formátu pre tokeny dátumu a času, ale rezervované rozsahy sú spoľahlivou páterou. Vypnite DetectDates a tabuľka štýlov sa vôbec nenačíta, každá číselná bunka prechádza ako xdkNumber a skenovanie je o niečo štíhlejšie
Preskočenie listov a predčasné ukončenie
Sekvenčné skenovanie má tichú výhodu, ktorej náhodný prístup nemôže konkurovať: môžete zastaviť. Udalosť OnSheet sa spustí pred otvorením každého pracovného listu a dáva vám dva prepínače. Nastavte SkipSheet a celá táto súčasť sa nikdy neanalyzuje – takto v zošite s viacerými listami skenujete iba listy, o ktoré vám ide, bez platby za čítanie zvyšku. Nastavte Abort a celé skenovanie sa ihneď skončí. Udalosť OnCell nesie vlastný Abort, takže môžete zastaviť v momente, keď ste našli to, čo ste hľadali – konkrétny riadok, sentinelovú hodnotu, koniec bloku hlavičiek – bez čítania zostávajúcich miliónov buniek. Pri doprednom skenovaní je prerušenie skutočne zadarmo, pretože práca, ktorú preskočíte, je práca, ktorá sa ešte nestala
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;Počítanie buniek bez obslužnej rutiny
Jedno nedávne vylepšenie stojí za zmienku, pretože mení bežnú otázku na jediné lacné volanie. Čítač počíta každú obsadenú bunku, ktorou prechádza, a robí to bez ohľadu na to, či je pripojená obslužná rutina OnCell. Predtým, keď nebola nastavená žiadna obslužná rutina, počet obsadených buniek sa vracal ako nula, pretože počítanie bolo vedľajším efektom vysielania. Teraz je počítanie nezávislé od vysielania. To znamená, že môžete položiť jednu otázku – koľko obsadených buniek tento zošit skutočne obsahuje – a dostať odpoveď za cenu skenovania bez akýchkoľvek spätných volaní. ReadFile aj ReadStream vracajú tento súčet ako Int64 a rovnaké číslo je potom dostupné ako vlastnosť CellCount. Návratová hodnota -1 signalizuje, že súbor sa nedal otvoriť alebo nie je balíkom OOXML
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;Pre úplné skenovanie pripojíte obslužnú rutinu a zavoláte ReadFile úplne rovnakým spôsobom. Kontrast s plným načítaním je celou podstatou: zatiaľ čo načítanie quarterly_export.xlsx do zošita by rozvinulo každú bunku do rezidentného objektu a udržiavalo celok, priamy čítač udržiava iba zdieľané reťazce a tabuľku štýlov, kým dvanásť miliónov buniek plynie cez váš OnCell jednu po druhej. Výpočet, ktorý prebehol na bunku, nezanecháva nič, takže maximálna pamäť je určená počtom odlišných reťazcov zošita, nie jeho počtom riadkov
Priamy čítač je správny nástroj, keď je úloha prečítať veľký zošit raz a extrahovať alebo sumarizovať ho. Keď naopak potrebujete náhodný prístup plného modelu, ale chcete, aby sa správal na veľkých súboroch, ladenie v našich poznámkach o výkone veľkých zošitov v Delphi pokrýva túto cestu. A keď je smer obrátený – produkcia veľkého výstupu namiesto jeho konzumácie – návod na streamované zapisovanie pre serverové dávkové úlohy uplatňuje rovnakú disciplínu konštantnej pamäte pri zápise. Všetky tri sú súčasťou HotXLS Component pre Delphi a C++Builder, spolu s API pre čítanie, zápis, vzorce a formátovanie, ktoré sú popísané inde na tomto blogu