Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Sheet9// File : Packages/SheetIO/Tests/SheetIOTests/PerformanceTests.swift10// Module : SheetIOTests11// Repository : https://git.spboucher.ai/orix-sheet12// Created : 2026-11-2113// Last Modified : 2026-11-2114//15// =============================================================================16// AUTHOR & PROFESSIONAL AFFILIATION17// =============================================================================18//19// Author : Simon-Pierre Boucher20// Position : Professor of Finance21// Institution : Université du Québec en Outaouais (UQO)22// Department : Département des sciences administratives23// Organization : SP Boucher24//25// Website : https://spboucher.ai26// Git : https://git.spboucher.ai27//28// =============================================================================29// OFFICIAL CONTACT DIRECTORY30// =============================================================================31//32// General : info@spboucher.ai33// Professional : contact@spboucher.ai34// Direct : simon@spboucher.ai35// Research : research@spboucher.ai36// Development : dev@spboucher.ai37// Support : support@spboucher.ai38// Security/Admin : admin@spboucher.ai39// Notifications : notifications@spboucher.ai40//41// Preferred Contact: support@spboucher.ai42//43// =============================================================================44// COPYRIGHT & INTELLECTUAL PROPERTY45// =============================================================================46//47// Copyright (c) 202648// Simon-Pierre Boucher49//50// All rights reserved where applicable.51//52// This file is governed by the applicable project license,53// copyright notices, institutional agreements and relevant law.54//55// Unless expressly authorized by the applicable license or56// rights holder, copying, redistribution, sublicensing,57// publication and commercial exploitation are not permitted.58//59// Existing third-party rights and notices remain unaffected.60//61// =============================================================================62// CONFIDENTIALITY & SECURITY63// =============================================================================64//65// Classification : Public66// Distribution : Public repository; use is governed by the project license67// Security Contact : admin@spboucher.ai68//69// Access to this source does not independently grant permission70// to redistribute or disclose confidential materials.71//72// Do not embed credentials, private keys, access tokens,73// certificates or confidential personal information.74//75// =============================================================================76// LICENSE & MAINTENANCE77// =============================================================================78//79// License : None specified (no license file in this repository)80// Maintainer : Simon-Pierre Boucher81// Technical Contact: dev@spboucher.ai82//83// =============================================================================84// END OF SP BOUCHER SOURCE HEADER85// =============================================================================8687import Foundation88import Testing89import SheetCore90@testable import SheetIO9192/// Timings are printed with a `[bench]` prefix. The 1,000,000-cell runs are behind `ORIX_BENCH=1`:93/// ORIX_BENCH=1 swift test -c release -Xswiftc -enable-testing --filter PerformanceTests94@Suite(.serialized) struct PerformanceTests {95 static var heavy: Bool { ProcessInfo.processInfo.environment["ORIX_BENCH"] == "1" }9697 /// rows × 10 columns: 6 numeric, 2 text (one unique, one repeated), 1 date, 1 formula.98 static func makeSheet(rows: Int) -> Workbook {99 let wb = Workbook(sheetNames: ["Data"])100 var date = CellStyle(); date.numberFormat = "yyyy-mm-dd"101 let dateID = wb.styles.intern(date)102 var money = CellStyle(); money.numberFormat = "#,##0.00"103 let moneyID = wb.styles.intern(money)104 let cities = ["Montréal", "Québec", "Gatineau", "Sherbrooke", "Trois-Rivières", "Saguenay", "Lévis", "Laval"]105 var columns = [[Int: Cell]](repeating: [:], count: 10)106 for c in 0..<10 { columns[c].reserveCapacity(rows) }107 var rng = SplitMix64(seed: 1)108 for r in 0..<rows {109 columns[0][r] = Cell(value: .number(Double(r)))110 columns[1][r] = Cell(value: .number(Double.random(in: 0...1000, using: &rng)), style: moneyID)111 columns[2][r] = Cell(value: .number(Double(Int.random(in: 0...100_000, using: &rng)) / 100))112 columns[3][r] = Cell(value: .number(Double.random(in: -1...1, using: &rng)))113 columns[4][r] = Cell(value: .number(Double(r % 97)))114 columns[5][r] = Cell(value: .number(1e6 + Double(r) * 0.25))115 columns[6][r] = Cell(value: .text("customer-\(r)"))116 columns[7][r] = Cell(value: .text(cities[r % cities.count]))117 columns[8][r] = Cell(value: .number(45000 + Double(r % 365)), style: dateID)118 let f = Formula(expr: try! FormulaParser.parse("B\(r + 1)*C\(r + 1)+E\(r + 1)"))119 f.value = .number(Double(r) * 0.5)120 columns[9][r] = Cell(formula: f)121 }122 for c in 0..<10 { wb.sheets[0].cells.replaceColumn(c, with: columns[c]) }123 return wb124 }125126 static func peakRSSMegabytes() -> Double {127 var usage = rusage()128 getrusage(RUSAGE_SELF, &usage)129 return Double(usage.ru_maxrss) / 1_048_576130 }131132 static func time<T>(_ body: () throws -> T) rethrows -> (T, Double) {133 let t = Date()134 let v = try body()135 return (v, Date().timeIntervalSince(t))136 }137138 func run(rows: Int, label: String) throws {139 let dir = Scratch.dir("bench")140 defer { try? FileManager.default.removeItem(at: dir) }141 let wb = Self.makeSheet(rows: rows)142 let cells = rows * 10143 var o = WorkbookCompareOptions()144 o.layout = false; o.features = false; o.maxDifferences = 3145 func report(_ format: String, _ write: Double, _ read: Double, _ url: URL) {146 let size = (try? FileManager.default.attributesOfItem(atPath: url.path)[.size] as? Int) ?? directorySize(url)147 print(String(format: "[bench] %@ %@ cells=%d write=%.2fs (%.0f cells/s) read=%.2fs (%.0f cells/s) size=%.1f MB peakRSS=%.0f MB",148 label, format, cells, write, Double(cells) / write, read, Double(cells) / read, Double(size ?? 0) / 1_048_576, Self.peakRSSMegabytes()))149 }150 func directorySize(_ url: URL) -> Int? {151 guard let e = FileManager.default.enumerator(at: url, includingPropertiesForKeys: [.fileSizeKey]) else { return nil }152 var total = 0153 for case let f as URL in e { total += (try? f.resourceValues(forKeys: [.fileSizeKey]).fileSize) ?? 0 }154 return total155 }156157 let native = dir.appendingPathComponent("b.orixsheet")158 let (_, nw) = try Self.time { try NativeFormat.write(wb, to: native) }159 let (nr, nrt) = try Self.time { try NativeFormat.read(url: native) }160 #expect(nr.workbook.sheets[0].cells.count == cells)161 #expect(WorkbookDiff.differences(wb, nr.workbook, options: .exact).isEmpty)162 var size = directorySize(native) ?? 0163 print(String(format: "[bench] %@ orixsheet cells=%d write=%.2fs (%.0f cells/s) read=%.2fs (%.0f cells/s) size=%.1f MB peakRSS=%.0f MB",164 label, cells, nw, Double(cells) / nw, nrt, Double(cells) / nrt, Double(size) / 1_048_576, Self.peakRSSMegabytes()))165166 let xlsx = dir.appendingPathComponent("b.xlsx")167 let (_, xw) = try Self.time { try WorkbookWriter.save(wb, to: xlsx, format: .xlsx) }168 let (xr, xrt) = try Self.time { try WorkbookReader.load(url: xlsx) }169 #expect(xr.workbook.sheets[0].cells.count == cells)170 #expect(WorkbookDiff.differences(wb, xr.workbook, options: o).isEmpty)171 report("xlsx", xw, xrt, xlsx)172173 let csv = dir.appendingPathComponent("b.csv")174 let (_, cw) = try Self.time { try WorkbookWriter.save(wb, to: csv, format: .csv) }175 let (cr, crt) = try Self.time { try WorkbookReader.load(url: csv) }176 #expect(cr.workbook.sheets[0].cells.count == cells)177 report("csv", cw, crt, csv)178179 if rows <= 200_000 {180 let ods = dir.appendingPathComponent("b.ods")181 let (_, ow) = try Self.time { try WorkbookWriter.save(wb, to: ods, format: .ods) }182 let (or, ort) = try Self.time { try WorkbookReader.load(url: ods) }183 #expect(or.workbook.sheets[0].cells.count == cells)184 report("ods", ow, ort, ods)185 let json = dir.appendingPathComponent("b.jsonl")186 var so = SaveOptions(); so.json.shape = .arrays187 let (_, jw) = try Self.time { try WorkbookWriter.save(wb, to: json, format: .jsonl, options: so) }188 let (jr, jrt) = try Self.time { try WorkbookReader.load(url: json) }189 #expect(jr.workbook.sheets[0].cells.count == cells)190 report("jsonl", jw, jrt, json)191 }192 size = 0193 }194195 @Test func hundredThousandCells() throws { try run(rows: 10_000, label: "100k") }196197 @Test(.enabled(if: PerformanceTests.heavy)) func oneMillionCells() throws { try run(rows: 100_000, label: "1M") }198199 @Test(.enabled(if: PerformanceTests.heavy)) func fiveMillionCellsNativeAndXLSX() throws {200 let dir = Scratch.dir("bench5")201 defer { try? FileManager.default.removeItem(at: dir) }202 let wb = Self.makeSheet(rows: 500_000)203 let native = dir.appendingPathComponent("b.orixsheet")204 let (_, nw) = try Self.time { try NativeFormat.write(wb, to: native) }205 let (nr, nrt) = try Self.time { try NativeFormat.read(url: native) }206 #expect(nr.workbook.sheets[0].cells.count == 5_000_000)207 print(String(format: "[bench] 5M orixsheet write=%.2fs read=%.2fs peakRSS=%.0f MB", nw, nrt, Self.peakRSSMegabytes()))208 let xlsx = dir.appendingPathComponent("b.xlsx")209 let (_, xw) = try Self.time { try WorkbookWriter.save(wb, to: xlsx, format: .xlsx) }210 let (xr, xrt) = try Self.time { try WorkbookReader.load(url: xlsx) }211 #expect(xr.workbook.sheets[0].cells.count == 5_000_000)212 print(String(format: "[bench] 5M xlsx write=%.2fs read=%.2fs peakRSS=%.0f MB", xw, xrt, Self.peakRSSMegabytes()))213 }214}215