1//go:build stress23// Stress benchmarks of formulas and pivot tables over linked sources of4// ten million rows (stress.SalesParquet, stress.SalesSQLite): each5// question answered from scratch by a job, as the screen's host runs it6// in the background, with the peak heap it took. Timed under the bench7// lock.8package paged910import (11 "context"12 "testing"1314 "src.rest/cat/012/internal/sheet"15 "src.rest/cat/012/internal/stress"16)1718// bigRows is the rows of the generated sources.19const bigRows = 10_000_0002021// bigFormulas are the formulas measured, reading the source as sales:22// A id, B cat, C amount, D day.23var bigFormulas = []struct{ name, formula string }{24 {"SUM", "=SUM(sales[amount])"},25 {"AVERAGE", "=AVERAGE(sales[amount])"},26 {"COUNTIFS", `=COUNTIFS(sales[cat],"north",sales[amount],">5000")`},27 {"SUMIFS", `=SUMIFS(sales[amount],sales[cat],"east")`},28 {"SUMPRODUCT", "=SUMPRODUCT(sales[amount],sales[id])"},29 {"XLOOKUP-last", "=XLOOKUP(9999999,sales[id],sales[amount])"},30 {"MATCH-middle", "=MATCH(5000000,sales[id],0)"},31 {"MEDIAN", "=MEDIAN(sales[amount])"},32 {"PERCENTILE", "=PERCENTILE(sales[amount],0.9)"},33 {"MODE", "=MODE(sales[amount])"},34 {"LARGE", "=LARGE(sales[amount],1000)"},35 {"RANK", "=RANK(5000,sales[amount])"},36 {"STDEV-held", "=STDEV(sales[amount])"},37}3839func BenchmarkSourceFormulas(b *testing.B) {40 dir := stress.GeneratedDir(b.TempDir())41 pq, err := stress.SalesParquet(dir, bigRows)42 if err != nil {43 b.Fatal(err)44 }45 db, err := stress.SalesSQLite(dir, bigRows)46 if err != nil {47 b.Fatal(err)48 }49 defer stress.BenchLock(b)()50 for _, src := range []struct{ kind, path string }{{"parquet", pq}, {"sqlite", db}} {51 b.Run(src.kind, func(b *testing.B) {52 for _, f := range bigFormulas {53 b.Run(f.name, func(b *testing.B) { benchFormula(b, src.path, f.formula) })54 }55 b.Run("pivot-cat", func(b *testing.B) { benchPivot(b, src.path) })56 })57 }58}5960// bigBook is a workbook linking the source at path as sales, opened by61// a host of its own.62func bigBook(b *testing.B, path string) (*sheet.Workbook, *Host) {63 w := sheet.NewBook()64 if _, err := w.AddSource("sales", sheet.LinkSource{Path: path}, w.Sheet(0)); err != nil {65 b.Fatal(err)66 }67 h := NewHost(sheet.DefaultMaxCells)68 h.Settle(context.Background(), w, Links(w, same, b.TempDir()))69 if info, _ := w.LookupSource("sales"); info.Shape.Rows != bigRows {70 b.Fatalf("the source has %d rows: %s", info.Shape.Rows, info.Err)71 }72 return w, h73}7475// benchFormula times the job answering formula over the source.76func benchFormula(b *testing.B, path, formula string) {77 w, h := bigBook(b, path)78 defer h.Close()79 s := w.Sheet(0)80 var v sheet.Value81 measure(b, func() {82 h.forget("sales")83 s.Set(sheet.Addr{}, "")84 s.Set(sheet.Addr{}, formula)85 h.Settle(context.Background(), w, nil)86 v = s.Value(sheet.Addr{})87 })88 if sheet.IsPending(v) {89 b.Fatalf("%s never answered", formula)90 }91 b.ReportMetric(float64(bigRows)*float64(b.N)/b.Elapsed().Seconds(), "rows/s")92}9394// benchPivot times a pivot table of the amounts by category.95func benchPivot(b *testing.B, path string) {96 w, h := bigBook(b, path)97 defer h.Close()98 info, _ := w.LookupSource("sales")99 r := sheet.Rect{To: sheet.Addr{Col: 3, Row: sheet.MaxRows - 1}}100 p := sheet.NewPivot(info.Sheet, r)101 p.Rows = []sheet.PivotGroup{{Col: 1}}102 p.Values = []sheet.PivotValue{{Col: 2, Summarize: sheet.SumBy}, {Col: 2, Summarize: sheet.CountBy}}103 measure(b, func() {104 h.forget("sales")105 pt, err := w.CreatePivot(info.Sheet, r, "", p)106 if err != nil {107 b.Fatal(err)108 }109 h.Settle(context.Background(), w, nil)110 if v := pt.Value(sheet.Addr{Row: 9}); v.Kind != sheet.Text {111 b.Fatalf("no Grand Total row: %v", v)112 }113 w.DeleteSheet(pt)114 })115}116117// measure runs fn in the benchmark's loop, reporting the peak heap it118// took above what was live before.119func measure(b *testing.B, fn func()) {120 before := stress.LiveHeap()121 stop := stress.SamplePeak()122 for b.Loop() {123 fn()124 }125 peak := stop()126 b.ReportMetric(float64(peak-min(peak, before))/(1<<20), "peak-MB")127}