Performanse velikih Excel radnih svezaka u Delphi-ju pomoću HotXLS-a

Kada izvoz od 300.000 redova probije budžet memorije, obično se krivi broj redova. Taj broj je uglavnom nevin. Skupi delovi velike radne sveske su oni koji se kreiraju kao nuspojava: baza stilova koja raste za po jedan unos po ćeliji jer je formatiranje dodavano unutar petlje, XML radnog lista sastavljen kao jedan ogroman string u vreme čuvanja, i milion identičnih tela formula koja se čuvaju jedno po jedno. HotXLS, losLab-ova izvorna Delphi biblioteka za XLS i XLSX datoteke, daje vam specifičnu polugu za svaki od ovih troškova. Nijedna od njih nije podrazumevano uključena, jer svaka menja odnos između prednosti i mana (trade-off), pa je poznavanje toga koja poluga odgovara kom simptomu stvarna veština optimizacije performansi

Gde velika radna sveska troši memoriju

Postoje dva različita režima memorije o kojima treba razmišljati. Tokom generisanja, model ćelija u memoriji raste sa svakom ćelijom koju dodirnete: vrednosti, formati i formule postaju objekti ili unosi u bazama stilova. Tokom čuvanja, podrazumevana XLSX putanja dodatno renderuje XML svakog radnog lista u širok string (wide string) pre nego što ga komprimuje u zip kontejner, tako da je vršna potrošnja model plus serijalizovani oblik najvećeg lista. Posao koji preživi petlju izgradnje, a zatim pukne unutar metode SaveAs, pogađa drugi režim memorije, a ne prvi, i popravka za jedan ne pomaže kod drugog

Veličina fajla prati slično pravilo: ćelije su samo jedan od doprinosilaca, pored stilova, deljenih stringova (shared strings), formula, slika i komentara. Revizija pomoću metode ForEachCell i broja kolekcija po listu govori vam koji resurs zapravo dominira problematičnim fajlom pre nego što optimizujete pogrešnu stvar. Jedna suptilnost kod merenja: svojstvo Sheet.Cells.Count na XLSX strani prijavljuje broj instanciranih ćelija u proređenom skladištu (sparse store), a ne površinu korišćenog opsega. List čiji podaci zauzimaju pravougaonik dimenzija 1000 sa 50 sa polovinom praznih ćelija broji oko 25.000 ćelija, a ne 50.000. Ta razlika je važna kada upoređujete klijentov "ogroman" fajl sa vašim test podacima (fixtures), jer se površina korišćenog opsega i stvarna naseljenost ćelijama mogu razlikovati za red veličine u proređenim finansijskim rasporedima

StreamingWrite popravlja putanju čuvanja, a ne izgradnje

Podešavanje TXLSXWorkbook.StreamingWrite := True prebacuje metodu SaveAs na strimujući serijalizator (streaming serializer) koji upisuje XML radnog lista direktno u zip tok (stream), eliminišući privremeni string po listu. Podrazumevano je postavljeno na False radi kompatibilnosti ponašanja, a njegovo uključivanje je izmena u jednoj liniji koda:

Book := TXLSXWorkbook.Create;
try
  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;
  end;
  Book.StreamingWrite := True;   // sheet XML streams into the zip container
  Book.SaveAs('bulk.xlsx');
finally
  Book.Free;
end;

Budite precizni oko toga šta ovim dobijate: model ćelija izgrađen u petlji zauzima potpuno isto memorije kao i ranije. StreamingWrite spljoštava vršnu potrošnju u vreme čuvanja, što je razlika između grupnog posla (batch job) koji se uspešno završava i onog koji pukne na 95% izvršenja. Ako sama petlja izgradnje iscrpi memoriju, poluge koje su vam potrebne su sledeće dve

Baze stilova: dodajte jednom, ponovo koristite indeks

Formatiranje u XLSX formatu kod HotXLS-a je zasnovano na bazama (pools): metode Book.Fonts.Add(...), Fills.AddSolid(...) i Borders.Add(...) vraćaju indeks baze koji počinje od 0, a koji ćelije referenciraju. Pozivanje metode Fonts.Add sa identičnim parametrima unutar petlje se deduplicira, pa više troši vreme nego prostor. Metoda Alignments.Add se ponaša drugačije: ona vraća novi objekat pri svakom pozivu, pa kreiranje poravnanja po ćeliji linearno povećava bazu sa brojem redova. Jedna navika pokriva oba slučaja: razrešite svaki indeks iz baze jednom, van petlje, i dodeljujte te indekse unutar nje

// hoist pool lookups out of the hot loop
HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);   // 0-based pool index
for C := 1 to 24 do
  Sheet.Cells[1, C].FontIndex := HeaderFont + 1;            // cells store 1-based; 0 = default

Izraz + 1 nije greška u kucanju, a zaboravljanje istog je klasičan bag koji generiše simptome: baze isporučuju indekse koji počinju od 0, dok svojstva na strani ćelije tretiraju 0 kao "default" (podrazumevano), tako da se svaki indeks iz baze mora pomeriti za jedan prilikom dodele. Ako ovo zaboravite, vaša zaglavlja će se tiho renderovati u podrazumevanom fontu radne sveske, što je nedostatak koji niko neće primetiti do provere vizuelnog identiteta

Zamenite saobraćaj Variant-a po ćeliji povratnim pozivima redova (row callbacks)

Svaki poziv Sheet.Cells[R, C].Value := X uključuje pronalaženje ili kreiranje ćelije plus dodelu tipa Variant. Na nivou od nekoliko stotina hiljada ćelija, taj trošak po pristupu postaje merljiv u profilima performansi. HotXLS nudi bulk API-je zasnovane na povratnim pozivima (callbacks) na obe fasade (ForEachCell i ForEachRow za čitanje, WriteCells and WriteRows za pisanje) koji pomeraju iteraciju unutar samog mehanizma i predaju vašem kodu čitave redove odjednom:

procedure TLedgerExport.FillRow(Sender: TObject;
  SheetIndex, Row, FirstCol, LastCol: Integer;
  var Values: Variant; var Skip: Boolean; var Cancel: Boolean);
begin
  if Row > FCount then
  begin
    Cancel := True;     // stop the whole write
    Exit;
  end;
  Values := VarArrayOf([FRows[Row - 1].Account,
                        FRows[Row - 1].PostedOn,
                        FRows[Row - 1].Amount]);
end;

// one engine call instead of hundreds of thousands of property hits
Sheet.WriteRows(1, 1, FCount, 3, FillRow);

Zastavica povratnog poziva Skip ostavlja red netaknutim bez prekidanja operacije, a Cancel završava operaciju ranije, što je korisno kada je izvor čitač čiju dužinu otkrivate u hodu. Uparite metodu WriteRows za izgradnju sa StreamingWrite za čuvanje i putanja generisanja više neće imati uskih grla po ćeliji

Poluge na strani čitanja na XLS fasadi

Veliki stariji .xls fajlovi imaju sopstveni komplet alata. Podešavanje _DisableGraphics := True pre poziva Open u potpunosti preskače parsovanje grafičkog sloja, što ubrzava učitavanje radnih svezaka koje nose godine akumuliranih oblika i ugrađenih slika. Ograničenje je strogo: grafički sloj je tada odsutan iz modela, tako da čuvanje takve radne sveske upisuje fajl bez njegovih crteža. Rezervišite ovu zastavicu za poslove analize koji su samo za čitanje. Metoda SetTempDir preusmerava privremene fajlove BIFF pisača, što je važno na serverima gde podrazumevana lokacija za temp fajlove ima kvotu ili se nalazi na sporom skladištu. UseSharedFormulas grupiše ponovljena tela formula u zapise deljenih formula (shared-formula records), smanjujući fajlove u kojima se kolona sa formulama ponavlja kroz šezdeset hiljada redova

Petlje čitanja preko XLS podataka imaju zamku indeksiranja koju vredi istaći jer duplira posao kada se njome rukuje defanzivno, a kvari rezultate kada se propusti: UsedRange prijavljuje svoje granice FirstRow, LastRow, FirstCol i LastCol u bazi od 0, dok je Cells.Item[Row, Col] u bazi od 1. Skeniranje koje prolazi kroz korišćeni opseg mora dodati jedan svakoj koordinati prilikom pristupa ćeliji, kao u Cells.Item[Row + 1, Col + 1], jer će u suprotnom čitati mrežu pomerenu dijagonalno za jednu ćeliju, tiho ispuštajući poslednji red i kolonu i uključujući fantomski prvi red i kolonu. Povratni poziv ForEachCell u potpunosti zaobilazi ovo nepoklapanje, što je još jedan razlog više da ga preferirate za skeniranje celog lista

Sondiranje (probing) fajlova pre njihovog učitavanja

Najjeftinija operacija sa velikom radnom sveskom je ona koju izbegnete. Metoda GetSheetNames na obe fasade izlistava radne listove fajla bez učitavanja podataka o ćelijama. XLSX implementacija čita samo manifest radne sveske unutar zip-a i eksplicitno ostavlja instancu radne sveske nepopunjenom, a XLS fasada zaustavlja skeniranje na prvoj granici podtoka (substream). To ga čini pravim preliminarnim testom za pitanje "koji list bi ovaj posao uvoza trebao da cilja", dok CanReadEncrypted daje odgovor na pitanje "da li je ovo šifrovani kontejner" pre osuđenog pokušaja poziva Open

Names := TStringList.Create;
Book := TXLSXWorkbook.Create;
try
  if Book.GetSheetNames('big-unknown.xlsx', Names) <= 0 then
    raise Exception.Create('cannot enumerate sheets');   // failure clears the list
  // pick the target sheet, then decide whether a full Open is worth it
finally
  Book.Free;
  Names.Free;
end;

Obratite pažnju na konvenciju povratnog koda: ove funkcije sondiranja signaliziraju neuspeh vrednostima koje su manje ili jednake nuli i prazne izlaznu listu, pa testirajte <= 0 umesto poređenja sa jednom specifičnom vrednošću uspeha

Prilagođavanje pristupa obimu posla

Za automatske cevovode koji generišu mnogo velikih fajlova u nizu, još dve navike upotpunjuju sliku. Objekti radnih svezaka nisu bezbedni za niti (thread-safe) za deljenje, ali ništa vas ne sprečava da koristite jednu nezavisnu radnu svesku po radnoj niti (worker thread), što čisto paralelizuje grupnu konverziju. A kada izlaz ide na HTTP umesto na disk, preopterećenja metode čuvanja TStream kombinuju se sa StreamingWrite, tako da se veliki odgovor nikada ne materijalizuje kao privremeni fajl na disku. Važi jedna operativna fusnota: čuvanje u tok (stream) piše od trenutne pozicije bez vraćanja unazad, pa postavite Position := 0 pre nego što predate tok radnom okviru odgovora. Članak o strimujućem pisanju i grupnim poslovima razvija taj serverski obrazac, a članak o izvozu baza podataka prikazuje gde se ove poluge uklapaju u izveštaj vođen skupom podataka (dataset)

Na kraju, držite jedan worst-case test podatak (fixture) po porodici izveštaja i merite njegovo vreme u CI sistemu. Regresije performansi u generisanju dokumenata se retko same najavljuju. Stil dodat unutar petlje ili sonda zamenjena punim pozivom Open ne menja ništa funkcionalno, a noćni grupni proces jednostavno traje četrdeset minuta duže. Test sa merenjem vremena na reprezentativnom test podatku od pola miliona ćelija pretvara to odstupanje u crveni build (prijavu neuspeha) umesto u operativni incident u produkciji

Probne verzije, demo projekti sa primerom masovnog generisanja i kompletna API referenca dostupni su na stranici HotXLS komponenta