askrobots / dbbasic-tsv
Public
A database in TSV text files: data you can read, fast inserts, and an optional Rust speed-up.
rust_vs_sqlite.py · 8.0 KB · 240 lines
| 1 | #!/usr/bin/env python3 |
| 2 | """ |
| 3 | Benchmark: Pure Python TSV vs Rust-Accelerated TSV vs SQLite |
| 4 | Shows real performance with actual Rust acceleration |
| 5 | """ |
| 6 | |
| 7 | import os |
| 8 | import sys |
| 9 | import time |
| 10 | import sqlite3 |
| 11 | import tempfile |
| 12 | from pathlib import Path |
| 13 | |
| 14 | sys.path.insert(0, str(Path(__file__).parent.parent)) |
| 15 | |
| 16 | # Test if Rust is available |
| 17 | try: |
| 18 | import dbbasic_rust |
| 19 | RUST_AVAILABLE = True |
| 20 | print("✅ Rust acceleration module loaded!") |
| 21 | except ImportError: |
| 22 | RUST_AVAILABLE = False |
| 23 | print("❌ Rust module not available") |
| 24 | |
| 25 | from dbbasic import TSV |
| 26 | |
| 27 | |
| 28 | class RustAcceleratedTSV(TSV): |
| 29 | """TSV with Rust acceleration for hot paths""" |
| 30 | |
| 31 | def __init__(self, *args, **kwargs): |
| 32 | super().__init__(*args, **kwargs) |
| 33 | self.rust_enabled = RUST_AVAILABLE |
| 34 | |
| 35 | def insert_many(self, records): |
| 36 | """Use Rust for batch inserts""" |
| 37 | if RUST_AVAILABLE and len(records) > 100: |
| 38 | # Convert records to format Rust expects |
| 39 | rust_records = [ |
| 40 | {col: str(rec.get(col, "")) for col in self.columns} |
| 41 | for rec in records |
| 42 | ] |
| 43 | |
| 44 | # Use Rust for fast TSV writing |
| 45 | dbbasic_rust.write_tsv_batch( |
| 46 | str(self.data_file), |
| 47 | self.columns, |
| 48 | rust_records |
| 49 | ) |
| 50 | |
| 51 | # Update index |
| 52 | for i, record in enumerate(rust_records): |
| 53 | row_num = self.row_count + i |
| 54 | if "id" in record: |
| 55 | self.index[record["id"]].append(row_num) |
| 56 | |
| 57 | self.row_count += len(records) |
| 58 | self._save_index() |
| 59 | return len(records) |
| 60 | else: |
| 61 | return super().insert_many(records) |
| 62 | |
| 63 | def query(self, **conditions): |
| 64 | """Use Rust for filtering if available""" |
| 65 | # For indexed queries and small datasets, use parent's optimized path |
| 66 | if ("id" in conditions and len(conditions) == 1) or self.row_count <= 1000: |
| 67 | return super().query(**conditions) |
| 68 | |
| 69 | # Use Rust for filtering large datasets with multiple conditions |
| 70 | if RUST_AVAILABLE: |
| 71 | all_records = list(self.all()) |
| 72 | # Convert to list of dicts for Rust |
| 73 | records_list = [dict(r) for r in all_records] |
| 74 | filtered = dbbasic_rust.filter_records(records_list, conditions) |
| 75 | return filtered |
| 76 | else: |
| 77 | return super().query(**conditions) |
| 78 | |
| 79 | |
| 80 | def format_time(seconds: float) -> str: |
| 81 | """Format time for display""" |
| 82 | if seconds < 0.001: |
| 83 | return f"{seconds * 1000000:.0f}μs" |
| 84 | elif seconds < 1: |
| 85 | return f"{seconds * 1000:.2f}ms" |
| 86 | else: |
| 87 | return f"{seconds:.2f}s" |
| 88 | |
| 89 | |
| 90 | def run_comparison(): |
| 91 | print("=" * 80) |
| 92 | print("REAL Performance: Python vs Rust-Accelerated TSV vs SQLite") |
| 93 | print("=" * 80) |
| 94 | print() |
| 95 | |
| 96 | test_sizes = [1000, 10000, 50000] |
| 97 | |
| 98 | for size in test_sizes: |
| 99 | print(f"\n📊 Testing with {size:,} records") |
| 100 | print("-" * 60) |
| 101 | |
| 102 | # Generate test data |
| 103 | records = [ |
| 104 | {"id": str(i), "name": f"User_{i}", "email": f"user{i}@example.com", |
| 105 | "age": str(20 + (i % 50)), "created": str(time.time())} |
| 106 | for i in range(size) |
| 107 | ] |
| 108 | |
| 109 | # Test Pure Python TSV |
| 110 | print("\n🐍 Pure Python TSV:") |
| 111 | py_tsv = TSV("bench_python", ["id", "name", "email", "age", "created"]) |
| 112 | |
| 113 | start = time.perf_counter() |
| 114 | py_tsv.insert_many(records) |
| 115 | py_insert_time = time.perf_counter() - start |
| 116 | py_insert_rate = size / py_insert_time |
| 117 | print(f" Insert: {format_time(py_insert_time)} ({py_insert_rate:,.0f} records/sec)") |
| 118 | |
| 119 | # Query performance |
| 120 | query_count = min(100, size // 10) |
| 121 | start = time.perf_counter() |
| 122 | for i in range(query_count): |
| 123 | py_tsv.query_one(id=str(i * 10 % size)) |
| 124 | py_query_time = time.perf_counter() - start |
| 125 | py_query_rate = query_count / py_query_time |
| 126 | print(f" Query: {format_time(py_query_time)} ({py_query_rate:,.0f} queries/sec)") |
| 127 | |
| 128 | py_tsv.drop() |
| 129 | |
| 130 | if RUST_AVAILABLE: |
| 131 | # Test Rust-Accelerated TSV |
| 132 | print("\n🚀 Rust-Accelerated TSV:") |
| 133 | rust_tsv = RustAcceleratedTSV("bench_rust", ["id", "name", "email", "age", "created"]) |
| 134 | |
| 135 | start = time.perf_counter() |
| 136 | rust_tsv.insert_many(records) |
| 137 | rust_insert_time = time.perf_counter() - start |
| 138 | rust_insert_rate = size / rust_insert_time |
| 139 | print(f" Insert: {format_time(rust_insert_time)} ({rust_insert_rate:,.0f} records/sec)") |
| 140 | |
| 141 | # Query performance |
| 142 | start = time.perf_counter() |
| 143 | for i in range(query_count): |
| 144 | rust_tsv.query_one(id=str(i * 10 % size)) |
| 145 | rust_query_time = time.perf_counter() - start |
| 146 | rust_query_rate = query_count / rust_query_time |
| 147 | print(f" Query: {format_time(rust_query_time)} ({rust_query_rate:,.0f} queries/sec)") |
| 148 | |
| 149 | rust_tsv.drop() |
| 150 | |
| 151 | # Test SQLite |
| 152 | print("\n🗄️ SQLite:") |
| 153 | db_file = tempfile.NamedTemporaryFile(suffix=".db", delete=False) |
| 154 | conn = sqlite3.connect(db_file.name) |
| 155 | conn.execute(""" |
| 156 | CREATE TABLE bench ( |
| 157 | id TEXT PRIMARY KEY, |
| 158 | name TEXT, |
| 159 | email TEXT, |
| 160 | age INTEGER, |
| 161 | created REAL |
| 162 | ) |
| 163 | """) |
| 164 | |
| 165 | sqlite_records = [ |
| 166 | (r["id"], r["name"], r["email"], int(r["age"]), float(r["created"])) |
| 167 | for r in records |
| 168 | ] |
| 169 | |
| 170 | start = time.perf_counter() |
| 171 | conn.executemany("INSERT INTO bench VALUES (?, ?, ?, ?, ?)", sqlite_records) |
| 172 | conn.commit() |
| 173 | sqlite_insert_time = time.perf_counter() - start |
| 174 | sqlite_insert_rate = size / sqlite_insert_time |
| 175 | print(f" Insert: {format_time(sqlite_insert_time)} ({sqlite_insert_rate:,.0f} records/sec)") |
| 176 | |
| 177 | # SQLite query |
| 178 | cursor = conn.cursor() |
| 179 | start = time.perf_counter() |
| 180 | for i in range(query_count): |
| 181 | cursor.execute("SELECT * FROM bench WHERE id = ?", (str(i * 10 % size),)) |
| 182 | cursor.fetchone() |
| 183 | sqlite_query_time = time.perf_counter() - start |
| 184 | sqlite_query_rate = query_count / sqlite_query_time |
| 185 | print(f" Query: {format_time(sqlite_query_time)} ({sqlite_query_rate:,.0f} queries/sec)") |
| 186 | |
| 187 | conn.close() |
| 188 | os.unlink(db_file.name) |
| 189 | |
| 190 | # Show comparisons |
| 191 | print("\n📈 Performance Comparison:") |
| 192 | |
| 193 | if RUST_AVAILABLE: |
| 194 | print(f"\n Rust vs Python TSV:") |
| 195 | print(f" Insert: {py_insert_time/rust_insert_time:.1f}x faster") |
| 196 | print(f" Query: {py_query_time/rust_query_time:.1f}x faster") |
| 197 | |
| 198 | print(f"\n Rust TSV vs SQLite:") |
| 199 | rust_vs_sqlite_insert = rust_insert_rate / sqlite_insert_rate |
| 200 | rust_vs_sqlite_query = rust_query_rate / sqlite_query_rate |
| 201 | print(f" Insert: {rust_vs_sqlite_insert:.1f}x {'faster' if rust_vs_sqlite_insert > 1 else 'slower'}") |
| 202 | print(f" Query: {rust_vs_sqlite_query:.1f}x {'faster' if rust_vs_sqlite_query > 1 else 'slower'}") |
| 203 | |
| 204 | print(f"\n Python TSV vs SQLite:") |
| 205 | py_vs_sqlite_insert = py_insert_rate / sqlite_insert_rate |
| 206 | py_vs_sqlite_query = py_query_rate / sqlite_query_rate |
| 207 | print(f" Insert: {py_vs_sqlite_insert:.1f}x {'faster' if py_vs_sqlite_insert > 1 else 'slower'}") |
| 208 | print(f" Query: {py_vs_sqlite_query:.1f}x {'faster' if py_vs_sqlite_query > 1 else 'slower'}") |
| 209 | |
| 210 | print("\n" + "=" * 80) |
| 211 | print("SUMMARY") |
| 212 | print("=" * 80) |
| 213 | |
| 214 | if RUST_AVAILABLE: |
| 215 | print(""" |
| 216 | ✅ Rust acceleration is working! |
| 217 | |
| 218 | The Rust module provides: |
| 219 | - Parallel TSV parsing with Rayon |
| 220 | - Fast hash-based filtering with AHash |
| 221 | - Buffered file I/O |
| 222 | - Zero-copy string handling where possible |
| 223 | |
| 224 | This is just the beginning - we could accelerate even more: |
| 225 | - Memory-mapped files for instant loading |
| 226 | - SIMD-accelerated tab finding |
| 227 | - Compiled query predicates |
| 228 | - B-tree indexes in Rust |
| 229 | """) |
| 230 | else: |
| 231 | print(""" |
| 232 | ❌ Rust module not loaded. To enable: |
| 233 | 1. cd rust |
| 234 | 2. maturin build --release |
| 235 | 3. pip install target/wheels/*.whl |
| 236 | """) |
| 237 | |
| 238 | |
| 239 | if __name__ == "__main__": |
| 240 | run_comparison() |