Predstavte si, že nočná služba v Delphi generuje jeden súbor XLSX pre každého zákazníka. Ide o niekoľko stoviek súborov, pričom niektoré z nich majú až 400 000 riadkov. Spustite analýzu výkonu a prekvapením bude málokedy cyklus plnenia buniek. Zvyčajne je to volanie metódy SaveAs. Pri predvolenom zapisovači sa každý hárok serializuje do jedného XML reťazca v pamäti predtým, ako sa tento skomprimuje do OOXML zip archívu, a pri širokom hárku môže dočasný reťazec zatieniť model buniek, z ktorého bol postavený. Úloha, ktorá pohodlne zostaví svoje dáta a usadí sa na hodnote 800 MB, tak pri ukladaní vyskočí nad 2 GB limit kontajnera a proces na čistenie pamäte (OOM killer) o tretej ráno, keď nikto nesleduje prevádzku, nahlási chybu. HotXLS, natívna knižnica pre tabuľky v Delphi od losLab, má vlastnosť zameranú priamo na túto špičku: StreamingWrite. Okolo nej sú k dispozícii ďalšie dva nástroje, ktoré rozhodujú o tom, či sa dávková úloha zmestí do svojho pamäťového a časového rozpočtu: spätné volania na úrovni riadkov a správanie fondu štýlov vnútri cyklu.
Čo vyrovnáva predvolená cesta a čo mení StreamingWrite
Predvolený zapisovač XLSX uprednostňuje jednoduchosť. Kompletne vykreslí XML hárka a potom odovzdá hotový reťazec zip kompresoru. Ide o správny kompromis pre veľkú väčšinu zošitov, kde sa XML celého hárka zmestí do niekoľkých megabajtov. Prestáva to však platiť, keď serializovaná podoba jedného hárka dosahuje stovky megabajtov. XML tabuliek je podrobné: každá číselná bunka stojí desiatky znakov značiek a reťazec, ktorý to všetko uchováva, musí byť súvislý. V grafe spotreby pamäte je tento priebeh jasne viditeľný: dlhá rovná plocha počas plnenia riadkov, potom prudký trojuholníkový nárast počas volania SaveAs a následný pokles po zapísaní zip archívu.
Nastavenie vlastnosti Book.StreamingWrite := True prepne zapisovač XLSX na taký, ktorý zapisuje XML kód hárka priamo do zip prúdu počas jeho generovania. Dočasný reťazec sa nikdy nealokuje a trojuholníkový nárast spotreby pamäte sa stratí v šume.
Buďte presní v tom, čo presne tým získate, pretože nadhodnotenie prínosu vedie k nesprávnym plánom kapacity. Tento príznak mení iba cestu ukladania. Zostavovanie zošita stále alokuje kompletný pamäťový model buniek, takže úroveň spotreby počas fázy plnenia je rovnaká ako predtým. Čo však zmizne, je serializačná špička, ktorá sa predtým pri uložení pripočítala k tejto úrovni, pričom pri úlohe plniacej 400 000 riadkov je táto špička bežne rozdielom medzi dodržaním pamäťového limitu a jeho prekročením. Táto vlastnosť je predvolene nastavená na False z dôvodu zachovania pôvodného správania, takže jej zapnutie je záležitosťou jedného explicitného zápisu v kóde.
Hromadný export so zapnutým príznakom
Book := TXLSXWorkbook.Create;
try
BoldIdx := Book.Fonts.Add('Calibri', 11, True, False); // pool index, 0-based
Sheet := Book.Sheets.Add('Bulk');
for R := 1 to 100000 do
begin
Sheet.Cells[R, 1].Value := R;
Sheet.Cells[R, 2].Value := 'Row ' + IntToStr(R);
Sheet.Cells[R, 3].Value := R * 1.5;
if (R mod 1000) = 0 then
Sheet.Cells[R, 2].FontIndex := BoldIdx + 1; // 1-based at the cell
end;
Book.StreamingWrite := True; // stream sheet XML straight into the zip
Book.SaveAs('bulk.xlsx');
finally
Book.Free;
end;Zápis Cells[R, C] vytvára bunky na požiadanie, čo udržiava telo cyklu čisté. Stojí za to zapamätať si dva limity mriežky: 1 048 576 riadkov a 16 384 stĺpcov, ktoré sú dostupné ako konštanty XlsxMaxRow a XlsxMaxCol. Dátový zdroj, ktorý prekročí limit riadkov, musí byť vo vašom kóde rozdelený na viacero hárkov. Nič v nadväzujúcom spracovaní toto prekročenie nezachytí ani neopraví za vás a súbor sa na limite jednoducho oreže.
Plnenie riadkov bez réžie typu Variant na každú bunku
Každé priradenie do Cells[R, C].Value platí za vyhľadanie bunky a konverziu typu Variant. Pri desiatich tisíckach riadkov si to nikto nevšimne. Pri milióne riadkov, z ktorých každý má dvadsať stĺpcov, sa táto réžia stáva dominantnou zložkou ceny fázy plnenia a analýza výkonu ukáže priamo na ňu. Hromadné rozhrania vám namiesto toho umožňujú odovzdať zapisovaču celý riadok naraz. Metóda WriteRows spúšťa spätné volanie, ktoré dodáva jeden riadok na jedno zavolanie:
procedure TBulkExporter.FillRow(Sender: TObject; SheetIndex, Row, FirstCol,
LastCol: Integer; var Values: Variant; var Skip: Boolean;
var Cancel: Boolean);
begin
if not FReader.Next then
begin
Cancel := True; // data source drained: stop cleanly
Exit;
end;
Values := VarArrayCreate([FirstCol, LastCol], varVariant);
Values[FirstCol] := FReader.RecordId;
Values[FirstCol + 1] := FReader.CustomerName;
Values[FirstCol + 2] := FReader.Amount;
end;
// fill rows 2..100001, columns A..C, pulling from the reader
Sheet.WriteRows(2, 1, 100001, 3, FillRow);Príznak Cancel je tým, čo mení pevný rozsah riadkov na „až do N riadkov“, čo je prirodzený stav, keď počet riadkov pochádza z dopytu, ktorého vykonávanie ste ešte nedokončili. Príznak Skip je jemnejší: ponechá konkrétny riadok prázdny bez prerušenia behu programu. Okrem plnenia buniek sa spätné volanie ukazuje ako dobré miesto pre prevádzkové záležitosti, ktoré sa inak zložito vkladajú do cyklu plnenia. Počítadlo priebehu, ktoré sa aktualizuje každých tisíc riadkov, kontrola požiadavky na zrušenie z plánovača úloh alebo obmedzovač rýchlosti čítania zo zdrojovej databázy: to všetko žije na jednom mieste namiesto toho, aby to bolo roztrúsené v kóde pre zápis buniek. Na strane čítania kopírujú rovnaký vzor metódy ForEachRow a ForEachCell, čo je dôležité, keď dávková úloha veľké súbory spotrebováva aj produkuje.
Fondy štýlov odmeňujú presun pred cyklus
Model štýlovania XLSX predstavuje sadu zdieľaných fondov. Metódy Fonts.Add, Fills.AddSolid a Borders.Add vracajú index vo fonde od nuly a bunka odkazuje na písmo uložením tohto indexu zvýšeného o jedna do vlastnosti FontIndex, kde nula je vyhradená pre predvolený štýl zošita. Zápis +1 je viditeľný aj v hromadnom príklade vyššie. Vynechajte ho a bunka ticho získa nesprávny štýl, pretože nesprávny index vo fonde štýlov je stále platným indexom a nevyvolá žiadnu výnimku.
Pravidlom, ktoré z toho vyplýva, je vytvoriť každý objekt štýlu pred cyklom riadkov a vnútri cyklu sa odkazovať už len na jeho index. Metóda Fonts.Add deduplikuje rovnaké definície, takže jej volanie pre každý riadok iba plytvá procesorovým časom. Pascou je Alignments.Add, pretože vracia nový záznam pri každom volaní. Vnútri cyklu so 100 000 riadkami to zaplaví súbor styles.xml stotisíc duplicitnými záznamami zarovnania, čo nafukuje veľkosť súboru na disku a spomaľuje každé neskoršie otvorenie v Exceli z dôvodu ich analýzy. Vytvorte každý štýl raz mimo cyklu a odkazujte sa na jeho index toľkokrát, koľkokrát potrebujete.
Prúdy, dočasné adresáre a dávkový cyklus okolo toho všetkého
Nič z toho nevyžaduje súborový systém. Obe rozhrania obsahujú preťaženia TStream naprieč celým svojím IO rozhraním (vrátane metód Open, SaveAs, SaveAsCSV, SaveAsHTML a SaveAsODS), takže dávková úloha môže zapisovať dáta priamo do TMemoryStream určeného pre úložisko objektov alebo sieťovú odpoveď bez zápisu na disk. Treba si pamätať na jednu dôležitú vec. Metóda SaveAs(Stream) zapisuje od aktuálnej pozície v prúde a neposúva sa na začiatok, preto pred odovzdaním prúdu nastavte vlastnosť Position := 0, inak príjemca prečíta nula bajtov. Rozhranie XLS pridáva dve vlastné nastavenia. Metóda SetTempDir nasmeruje dočasné súbory zapisovača BIFF na zväzok, ktorý má dostatok miesta a výkonu na ich spracovanie, čo je dôležité na serveroch, kde je predvolená systémová dočasná cesta umiestnená na obmedzenom disku. Vlastnosť UseSharedFormulas spája opakujúce sa telá vzorcov do zdieľaných skupín, čo výrazne zmenšuje veľkosť pri klasickom reporte, kde sa jeden vzorec kopíruje naprieč celým stĺpcom.
Samotný dávkový cyklus zostáva zámerne jednoduchý
for FileName in SourceFiles do
begin
Book := TXLSXWorkbook.Create; // fresh instance: no state bleed
try
Book.StreamingWrite := True;
if Book.Open(FileName) <> 1 then
Continue; // one bad input must not kill the batch
Book.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
finally
Book.Free;
end;
end;Nová inštancia zošita pre každý súbor stojí mikrosekundy a odstraňuje celú kategóriu chýb s prenosom stavu medzi súbormi: štýly, definované názvy a vlastnosti dokumentu zo súboru 17 nemajú šancu uniknúť do súboru 18. Preskočenie a pokračovanie pri zlyhaní metódy Open je rovnako dôležité, pretože jeden poškodený nahraný súbor v dávke 600 súborov by mal stáť len jeden záznam v logu a nie pád celého behu programu. Za zmienku stojí aj to, čo export do CSV zámerne nerobí. Metóda SaveAsCSV zapisuje vzorce ako doslovný text a nevyhodnocuje ich, takže dávka konverzie, ktorej odberatelia očakávajú vypočítané čísla, musí najprv spustiť metódu Calculate na príslušných bunkách alebo začať zo zošitov, ktoré už obsahujú uložené výsledky z predchádzajúceho výpočtu.
Model konkurentnosti: jeden zošit na jedno vlákno
Objekty oboch rozhraní nie sú vláknovo bezpečné a návrh to nikdy nepredstieral. Keďže medzi inštanciami neexistuje žiadny zdieľaný globálny stav, pravidlo škálovania je jednoduché: jeden zošit na jedno pracovné vlákno bez zdieľania zošita medzi vláknami. Skupina N vlákien, z ktorých každé vlastní svoj objekt TXLSXWorkbook, škáluje takmer lineárne, kým sa nedosiahne pamäťový strop. Tento strop sa dá vyčísliť: najväčší súbežný model buniek vynásobený počtom vlákien plus úspora, ktorú prinieslo vyhladenie špičiek cez StreamingWrite. Keď je front požiadaviek plný, aplikujte reguláciu na úrovni úloh (job queue) a nie vnútri zapisovača. Vlákno, ktoré napoly zapísalo zošit a vyčerpalo prostriedky, neprodukuje nič užitočné, zatiaľ čo úloha, ktorá počkala pár sekúnd na voľné vlákno, sa dokončí úspešne.
Pre širší pohľad na ladenie výkonu vrátane zdieľaných vzorcov, preskakovania grafiky na strane čítania a špecifík rozhrania XLS navštívte sprievodcu výkonom veľkých zošitov. Dávkové úlohy, ktorých riadky pochádzajú priamo z databázového dopytu, sú popísané samostatne v článku o exporte databáz pre reporty v Delphi.
HotXLS sa do vašej služby v Delphi alebo C++Builderi kompiluje ako natívny Object Pascal bez externých závislostí; edície a licencovanie nájdete na produktovej stránke komponentu HotXLS.