"""Reproducible high-cardinality SQLite benchmark. Synthetic, never real-data evidence. .venv/bin/python scripts/benchmark_queries.py --rows 4000000 --pipeline-rows 100000 4m raw CSV uses the real upload indexer; high-cardinality monthly is an explicit SQL fixture, not the result of classifying all 4m rows. Full pipeline is measured separately on --pipeline-rows unique declarations. Every phase records its scope. """ import argparse import csv import hashlib import json import math import os import platform import resource import sqlite3 import statistics import sys import time from pathlib import Path ROOT=Path(__file__).resolve().parents[1] sys.path.insert(0,str(ROOT/'tools-service')) def rss_mb(): value=resource.getrusage(resource.RUSAGE_SELF).ru_maxrss return round(value/(1024**2 if sys.platform=='darwin' else 1024),2) def disk_bytes(folder):return sum(p.stat().st_size for p in folder.rglob('*') if p.is_file()) def timed_query(conn,sql,args=(),repeats=7): timings=[];values=[] for _ in range(repeats): start=time.perf_counter(); values=[tuple(row) for row in conn.execute(sql,args)];timings.append((time.perf_counter()-start)*1000) return dict(sql=sql,parameters=list(args),plan=[r[3] for r in conn.execute('EXPLAIN QUERY PLAN '+sql,args)], first_ms=round(timings[0],3),p50_ms=round(statistics.median(timings),3), p95_ms=round(sorted(timings)[math.ceil(.95*len(timings))-1],3),rows=len(values), result_sha256=hashlib.sha256(json.dumps(values,default=str).encode()).hexdigest()) def write_csv(path,rows,model_count=None): model_count = model_count or max(1,rows//4) with path.open('w',encoding='utf-8',newline='') as handle: w=csv.writer(handle);w.writerow(['I_E_FLAG','HS_CODE','YEAR','WEEK','MONORSUM_USD','COUNT','G_MODEL','ORIGIN_COUNTRY','TRADE_AREA_CODE','TRADE_COUNTRY','DISTINATE_PORT']) for i in range(rows): cents=i%1000+1 w.writerow(['I' if i%5 else 'E',('8532241000','8542321010','8542321090','8542329010')[i%4], 2025+(i//52)%2,i%52+1,f'{cents//100}.{cents%100:02}',i%100+1, f'品牌:{("MURATA","SAMSUNG","MICRON","SK HYNIX")[i%4]};型号:TEST-{i%model_count:08}', f'X{i%197:03}',f'T{i%67:03}',f'D{i%89:03}',f'P{i%2003:04}']) def raw_phase(out,n): from toolsvc import objects from toolsvc.query_indexes import create_query_indexes source=out/'raw.csv';start=time.perf_counter();write_csv(source,n);generated=time.perf_counter()-start # Production importer, deliberately defer only business-index creation to obtain a baseline. original=objects.create_query_indexes;objects.create_query_indexes=lambda *a,**kw:None importer=objects.ObjectStore(out/'objects') start=time.perf_counter();columns,count=importer._index_raw('bench',source);ingest=time.perf_counter()-start objects.create_query_indexes=original conn=sqlite3.connect(out/'raw.sqlite'); conn.row_factory=None cases={ 'hs_year_week_seek':('SELECT _row_index,MONORSUM_USD FROM raw WHERE HS_CODE=? AND _num_YEAR=? AND _num_WEEK=? AND _row_index>? ORDER BY _row_index LIMIT 50',('8532241000','2025','1',int(n*.9))), 'year_week_seek':('SELECT _row_index,HS_CODE FROM raw WHERE _num_YEAR=? AND _num_WEEK=? AND _row_index>? ORDER BY _row_index LIMIT 50',('2025','1',int(n*.9))), 'deep_offset':('SELECT _row_index FROM raw ORDER BY _row_index LIMIT 50 OFFSET ?',(int(n*.9),)), 'deep_keyset':('SELECT _row_index FROM raw WHERE _row_index>=? ORDER BY _row_index LIMIT 50',(int(n*.9),)), } before={k:timed_query(conn,*v) for k,v in cases.items()} size_before=(out/'raw.sqlite').stat().st_size start=time.perf_counter();indexes=create_query_indexes(conn,'raw',[r[1] for r in conn.execute('PRAGMA table_info(raw)')],raw=True);conn.execute('ANALYZE');conn.commit();index_seconds=time.perf_counter()-start after={k:timed_query(conn,*v) for k,v in cases.items()} assert all(before[k]['result_sha256']==after[k]['result_sha256'] for k in cases) # Register the existing fixture without materializing the entire file in HTTP memory. from toolsvc.objects import store,now from toolsvc.db import database ws=store.create_workspace('query benchmark') with database.transaction() as app: app.execute('INSERT INTO objects(id,workspace_id,name,kind,media_type,path,sha256,bytes,rows,columns_json,producer,parents_json,meta_json,created_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)', ('bench_raw',ws['id'],'raw.csv','raw','text/csv',str(source),'sha256:synthetic-v1',source.stat().st_size,n,json.dumps(columns),'benchmark','[]',json.dumps({'sqlite_path':str(out/'raw.sqlite'),'lineage':{'rules_hash':'synthetic-query-fixture-v1','params_hash':str(n)}}),now())) app.execute('INSERT INTO tables VALUES(?,?,?,?,?)',('bench_raw','raw','raw',n,json.dumps(columns))) from toolsvc.readers import read_rows, QueryTimeout start=time.perf_counter();page=read_rows('bench_raw',limit=50,use_cache=False);page_ms=(time.perf_counter()-start)*1000 assert page['total']==n and page['total_exact'] and len(page['rows'])==50 first=read_rows('bench_raw',limit=50);second=read_rows('bench_raw',limit=50) assert second['cache_hit'] from toolsvc.config import settings deadline_before=settings.query_timeout_ms;settings.query_timeout_ms=100 start=time.perf_counter() try: read_rows('bench_raw',aggregate={'group_by':['G_MODEL'],'metrics':[dict(column='MONORSUM_USD',fn='sum')]},limit=20,use_cache=False) timeout='completed' except QueryTimeout:timeout='interrupted_as_expected' finally:settings.query_timeout_ms=deadline_before conn.close() return dict(scope='production _index_raw streaming path; excludes HTTP multipart buffering and business classification',rows=count, declared_distinct_models=n//4,distinct_declarations=n//4,generate_seconds=round(generated,3),indexer_without_business_indexes_seconds=round(ingest,3), business_index_seconds=round(index_seconds,3),business_indexes=indexes,sqlite_before_bytes=size_before,sqlite_after_bytes=(out/'raw.sqlite').stat().st_size, before=before,after=after,public_metadata_page_ms=round(page_ms,3),deadline_100ms_result=timeout, deadline_elapsed_ms=round((time.perf_counter()-start)*1000,3),cache_second_hit=second['cache_hit']) def pipeline_phase(out,n): from toolsvc.customs import CustomsProcessRequest,CustomsOptions,load_processor source=out/'pipeline.csv';write_csv(source,n,model_count=n) start=time.perf_counter() result=load_processor().process(CustomsProcessRequest(str(source),str(out/'pipeline'),options=CustomsOptions(directions=['I','E'],cutoff_date='2026-12-31'))) elapsed=time.perf_counter()-start from toolsvc.objects import store ws=store.create_workspace('pipeline lineage benchmark');parent=store.register_upload(ws['id'],'pipeline.csv',source.read_bytes()) assert parent['sha256'].removeprefix('sha256:')==result.lineage['input_sha256'] start=time.perf_counter();registered=store.register_processed(ws['id'],parent['id'],'result',out/'pipeline',result.to_dict());lineage=time.perf_counter()-start with sqlite3.connect(out/'pipeline/working.sqlite') as verify: distinct=verify.execute('SELECT COUNT(DISTINCT raw_model) FROM audit').fetchone()[0] return dict(scope='full actual rules+parser+ingest+original 23 reports+2 query summaries; lineage registration separately timed', rows=n,distinct_declarations=distinct,seconds=round(elapsed,3),stats=result.stats,lineage_seconds=round(lineage,3),result_rows=registered['rows'], workbook_sheets=23,query_tables=2,output_bytes=disk_bytes(out/'pipeline')) def monthly_phase(out,n): from toolsvc.customs.engine.storage import DIMENSIONS,connect from toolsvc.customs.engine.query_summaries import build_query_summaries from toolsvc.customs.engine.numbers import StrictSum from toolsvc.query_precision import register_precision db=connect(out/'monthly.sqlite') db.execute('CREATE TABLE monthly('+','.join(c+' TEXT' for c in DIMENSIONS)+',amount TEXT,quantity TEXT,n INTEGER)') insert='INSERT INTO monthly VALUES('+','.join('?' for _ in range(len(DIMENSIONS)+3))+')' start=time.perf_counter();batch=[] for i in range(n): m=(i//4)%24; cents=i%1000+1; cap=i%4==0 values=['I' if i%5 else 'E',str(2025+m//12),f'{2025+m//12}-{m%12+1:02}', '电容器' if cap else '存储器','MLCC' if cap else 'DRAM','' if cap else 'DDR','' if cap else 'DDR4', ('MURATA','SAMSUNG','MICRON','SK HYNIX')[i%4],f'TEST-{i%max(1,n//4):08}', '日本' if cap else '韩国', f'应用{i%9}',f'产国{i%197}',f'贸国{i%67}',f'启运{i%89}',f'港{i%2003}',f'港国{i%61}',str(int(not cap)), None if i%100003==0 else f'{cents//100}.{cents%100:02}',str(i%100+1),1] batch.append(values) if len(batch)>=5000:db.executemany(insert,batch);batch.clear() if batch:db.executemany(insert,batch) db.commit();load=time.perf_counter()-start cases={ 'class_year':('SELECT c2,dsum(amount),dsum(quantity) FROM monthly WHERE direction=? AND c1=? AND year=? GROUP BY c2',('I','存储器','2025')), 'one_model_timeline':('SELECT month,dsum(amount),dsum(quantity) FROM monthly WHERE direction=? AND brand=? AND model=? GROUP BY month',('I','SAMSUNG','TEST-00000001')), 'focus':('SELECT brand,dsum(amount) FROM monthly WHERE direction=? AND year=? AND c1=? AND c3=? GROUP BY brand',('I','2025','存储器','DDR')), } before={k:timed_query(db,*v,repeats=3) for k,v in cases.items()} start=time.perf_counter() for suffix,keys in [('class','direction,c1,c2,c3,c4,year'),('model','direction,brand,model,month'),('focus','direction,year,c1,c3,app,brand_country,brand')]:db.execute(f'CREATE INDEX idx_{suffix} ON monthly({keys})') db.execute('ANALYZE');db.commit();index_seconds=time.perf_counter()-start after={k:timed_query(db,*v,repeats=3) for k,v in cases.items()} assert all(before[k]['result_sha256']==after[k]['result_sha256'] for k in cases) from toolsvc.query_indexes import create_monthly_indexes start=time.perf_counter();create_monthly_indexes(db);db.commit();covering_seconds=time.perf_counter()-start final_focus=timed_query(db,*cases['focus'],repeats=3) assert final_focus['result_sha256']==after['focus']['result_sha256'] target=out/'summaries';target.mkdir();sqlite3.connect(target/'stat.sqlite').close() start=time.perf_counter();sheets=build_query_summaries(db,target);summary_seconds=time.perf_counter()-start stat=sqlite3.connect(target/'stat.sqlite');register_precision(stat);stat.create_aggregate('dsum',1,StrictSum) expected=tuple(db.execute('SELECT dsum(amount),dsum(quantity),SUM(n) FROM monthly').fetchone()) totals={} for name in ('q_class_month','q_model_month'): totals[name]=stat.execute(f'SELECT dsum("实际金额(美元)"),dsum("成交数量"),SUM(CAST("记录数" AS INTEGER)) FROM {name}').fetchone() assert totals[name]==expected queries={ 'class_timeline':('SELECT "年度","月度","实际金额(美元)" FROM q_class_month WHERE "进/出口"=? AND "二级类目"=? ORDER BY "_num_年度","_num_月度",_row_index',('I','DRAM')), 'model_timeline':('SELECT "年度","月度","实际金额(美元)" FROM q_model_month WHERE "进/出口"=? AND "品牌"=? AND "型号"=? ORDER BY "_num_年度","_num_月度",_row_index',('I','SAMSUNG','TEST-00000001')), 'month_top50':('SELECT "品牌","型号","实际金额(美元)" FROM q_model_month WHERE "进/出口"=? AND "_num_年度"=? AND "_num_月度"=? AND "二级类目"=? ORDER BY "实际金额(美元)" COLLATE DECIMAL_VALUE DESC,_row_index ASC LIMIT 50',('I','2025','1','DRAM')), } summary_queries={k:timed_query(stat,*v) for k,v in queries.items()} counts={s.sheet:s.rows for s in sheets} stat.close();db.close() return dict(scope='4m high-cardinality parsed monthly SQL fixture, NOT a 4m business parser/pipeline run',rows=n, model_cardinality=n//4,monthly_rows=n,load_seconds=round(load,3),existing_three_index_seconds=round(index_seconds,3), before=before,after=after,covering_focus=final_focus,covering_index_build_seconds=round(covering_seconds,3),query_summary_build_seconds=round(summary_seconds,3),summary_row_counts=counts, summary_queries=summary_queries,exact_summary_totals=totals,missing_amount_policy='global sum is NULL due to deliberate missing members', disk_bytes=disk_bytes(out)) def main(): parser=argparse.ArgumentParser();parser.add_argument('--rows',type=int,default=4000000);parser.add_argument('--pipeline-rows',type=int,default=100000) parser.add_argument('--out',type=Path,default=ROOT/'.run'/('bigdata-'+time.strftime('%Y%m%d-%H%M%S'))) parser.add_argument('--phase',choices=['all','raw','pipeline','monthly'],default='all');args=parser.parse_args() args.out=args.out.resolve();args.out.mkdir(parents=True,exist_ok=True) os.environ['CDA_DATA_DIR']=str(args.out/'service-data') result=dict(platform=platform.platform(),python=platform.python_version(),sqlite=sqlite3.sqlite_version,os_cache='not flushed; first connection and warm repeats; not cold-cache evidence',rows=args.rows,phases={}) if args.phase=='all': import subprocess for phase in ('raw','pipeline','monthly'): print('starting '+phase,flush=True) subprocess.run([sys.executable,__file__,'--phase',phase,'--rows',str(args.rows),'--pipeline-rows',str(args.pipeline_rows),'--out',str(args.out/phase)],check=True) result['phases'][phase]=json.loads((args.out/phase/'report.json').read_text()) (args.out/'report.json').write_text(json.dumps(result,ensure_ascii=False,indent=2)) else: fn={'raw':raw_phase,'pipeline':pipeline_phase,'monthly':monthly_phase}[args.phase] start=time.perf_counter();result['phases'][args.phase]=fn(args.out,args.pipeline_rows if args.phase=='pipeline' else args.rows) result.update(phase_wall_seconds=round(time.perf_counter()-start,3),peak_rss_mb=rss_mb(),total_disk_bytes=disk_bytes(args.out)) (args.out/'report.json').write_text(json.dumps(result,ensure_ascii=False,indent=2)) print(json.dumps(dict(phase=args.phase,wall_seconds=result['phase_wall_seconds'],peak_rss_mb=result['peak_rss_mb']),ensure_ascii=False),flush=True) print('report: '+str(args.out/'report.json'),flush=True) if __name__=='__main__':main()