askrobots / dbbasic-tsv
Public
A database in TSV text files: data you can read, fast inserts, and an optional Rust speed-up.
compare_sqlite.py · 9.3 KB · 314 lines
| 1 | #!/usr/bin/env python3 |
| 2 | """ |
| 3 | Performance comparison: dbbasic-tsv vs SQLite |
| 4 | Tests various operations and measures performance differences |
| 5 | """ |
| 6 | |
| 7 | import os |
| 8 | import sys |
| 9 | import time |
| 10 | import sqlite3 |
| 11 | import tempfile |
| 12 | from pathlib import Path |
| 13 | |
| 14 | # Add parent to path for dbbasic import |
| 15 | sys.path.insert(0, str(Path(__file__).parent.parent)) |
| 16 | from dbbasic import TSV |
| 17 | |
| 18 | |
| 19 | class Benchmark: |
| 20 | """Base benchmark class""" |
| 21 | |
| 22 | def __init__(self, name: str): |
| 23 | self.name = name |
| 24 | self.results = {} |
| 25 | |
| 26 | def run(self, iterations: int = 1): |
| 27 | """Run benchmark with timing""" |
| 28 | start = time.perf_counter() |
| 29 | for _ in range(iterations): |
| 30 | self.execute() |
| 31 | elapsed = time.perf_counter() - start |
| 32 | return elapsed / iterations |
| 33 | |
| 34 | def execute(self): |
| 35 | """Override in subclasses""" |
| 36 | raise NotImplementedError |
| 37 | |
| 38 | |
| 39 | class TSVInsertBenchmark(Benchmark): |
| 40 | """TSV batch insert benchmark""" |
| 41 | |
| 42 | def __init__(self, num_records: int): |
| 43 | super().__init__(f"TSV Insert {num_records} records") |
| 44 | self.num_records = num_records |
| 45 | self.db = TSV("benchmark", ["id", "name", "email", "age", "created"]) |
| 46 | |
| 47 | def execute(self): |
| 48 | records = [ |
| 49 | { |
| 50 | "id": str(i), |
| 51 | "name": f"User_{i}", |
| 52 | "email": f"user{i}@example.com", |
| 53 | "age": str(20 + (i % 50)), |
| 54 | "created": str(time.time()) |
| 55 | } |
| 56 | for i in range(self.num_records) |
| 57 | ] |
| 58 | self.db.insert_many(records) |
| 59 | |
| 60 | def cleanup(self): |
| 61 | self.db.drop() |
| 62 | |
| 63 | |
| 64 | class SQLiteInsertBenchmark(Benchmark): |
| 65 | """SQLite batch insert benchmark""" |
| 66 | |
| 67 | def __init__(self, num_records: int): |
| 68 | super().__init__(f"SQLite Insert {num_records} records") |
| 69 | self.num_records = num_records |
| 70 | self.db_file = tempfile.NamedTemporaryFile(suffix=".db", delete=False) |
| 71 | self.conn = sqlite3.connect(self.db_file.name) |
| 72 | self.conn.execute(""" |
| 73 | CREATE TABLE benchmark ( |
| 74 | id TEXT PRIMARY KEY, |
| 75 | name TEXT, |
| 76 | email TEXT, |
| 77 | age INTEGER, |
| 78 | created REAL |
| 79 | ) |
| 80 | """) |
| 81 | |
| 82 | def execute(self): |
| 83 | records = [ |
| 84 | (str(i), f"User_{i}", f"user{i}@example.com", 20 + (i % 50), time.time()) |
| 85 | for i in range(self.num_records) |
| 86 | ] |
| 87 | self.conn.executemany( |
| 88 | "INSERT INTO benchmark VALUES (?, ?, ?, ?, ?)", |
| 89 | records |
| 90 | ) |
| 91 | self.conn.commit() |
| 92 | |
| 93 | def cleanup(self): |
| 94 | self.conn.close() |
| 95 | os.unlink(self.db_file.name) |
| 96 | |
| 97 | |
| 98 | class TSVQueryBenchmark(Benchmark): |
| 99 | """TSV query benchmark""" |
| 100 | |
| 101 | def __init__(self, num_records: int, num_queries: int): |
| 102 | super().__init__(f"TSV Query (from {num_records} records)") |
| 103 | self.num_records = num_records |
| 104 | self.num_queries = num_queries |
| 105 | self.db = TSV("query_bench", ["id", "name", "email", "age"]) |
| 106 | |
| 107 | # Pre-populate |
| 108 | records = [ |
| 109 | {"id": str(i), "name": f"User_{i}", "email": f"user{i}@example.com", "age": str(20 + (i % 50))} |
| 110 | for i in range(num_records) |
| 111 | ] |
| 112 | self.db.insert_many(records) |
| 113 | |
| 114 | def execute(self): |
| 115 | for i in range(self.num_queries): |
| 116 | query_id = str(i % self.num_records) |
| 117 | result = self.db.query_one(id=query_id) |
| 118 | |
| 119 | def cleanup(self): |
| 120 | self.db.drop() |
| 121 | |
| 122 | |
| 123 | class SQLiteQueryBenchmark(Benchmark): |
| 124 | """SQLite query benchmark""" |
| 125 | |
| 126 | def __init__(self, num_records: int, num_queries: int): |
| 127 | super().__init__(f"SQLite Query (from {num_records} records)") |
| 128 | self.num_records = num_records |
| 129 | self.num_queries = num_queries |
| 130 | self.db_file = tempfile.NamedTemporaryFile(suffix=".db", delete=False) |
| 131 | self.conn = sqlite3.connect(self.db_file.name) |
| 132 | |
| 133 | # Create table and index |
| 134 | self.conn.execute(""" |
| 135 | CREATE TABLE query_bench ( |
| 136 | id TEXT PRIMARY KEY, |
| 137 | name TEXT, |
| 138 | email TEXT, |
| 139 | age INTEGER |
| 140 | ) |
| 141 | """) |
| 142 | self.conn.execute("CREATE INDEX idx_id ON query_bench(id)") |
| 143 | |
| 144 | # Pre-populate |
| 145 | records = [ |
| 146 | (str(i), f"User_{i}", f"user{i}@example.com", 20 + (i % 50)) |
| 147 | for i in range(num_records) |
| 148 | ] |
| 149 | self.conn.executemany("INSERT INTO query_bench VALUES (?, ?, ?, ?)", records) |
| 150 | self.conn.commit() |
| 151 | |
| 152 | def execute(self): |
| 153 | cursor = self.conn.cursor() |
| 154 | for i in range(self.num_queries): |
| 155 | query_id = str(i % self.num_records) |
| 156 | cursor.execute("SELECT * FROM query_bench WHERE id = ?", (query_id,)) |
| 157 | result = cursor.fetchone() |
| 158 | |
| 159 | def cleanup(self): |
| 160 | self.conn.close() |
| 161 | os.unlink(self.db_file.name) |
| 162 | |
| 163 | |
| 164 | def format_time(seconds: float) -> str: |
| 165 | """Format time for display""" |
| 166 | if seconds < 0.001: |
| 167 | return f"{seconds * 1000000:.0f}μs" |
| 168 | elif seconds < 1: |
| 169 | return f"{seconds * 1000:.2f}ms" |
| 170 | else: |
| 171 | return f"{seconds:.2f}s" |
| 172 | |
| 173 | |
| 174 | def run_comparison(): |
| 175 | """Run all benchmarks and compare""" |
| 176 | print("=" * 70) |
| 177 | print("dbbasic-tsv vs SQLite Performance Comparison") |
| 178 | print("=" * 70) |
| 179 | print() |
| 180 | |
| 181 | results = [] |
| 182 | |
| 183 | # Test different record counts |
| 184 | test_sizes = [100, 1000, 10000] |
| 185 | |
| 186 | for size in test_sizes: |
| 187 | print(f"\n📊 Testing with {size:,} records") |
| 188 | print("-" * 50) |
| 189 | |
| 190 | # Insert benchmarks |
| 191 | print("\nInsert Performance:") |
| 192 | |
| 193 | tsv_insert = TSVInsertBenchmark(size) |
| 194 | tsv_time = tsv_insert.run() |
| 195 | print(f" TSV: {format_time(tsv_time)} ({size/tsv_time:,.0f} records/sec)") |
| 196 | |
| 197 | sqlite_insert = SQLiteInsertBenchmark(size) |
| 198 | sqlite_time = sqlite_insert.run() |
| 199 | print(f" SQLite: {format_time(sqlite_time)} ({size/sqlite_time:,.0f} records/sec)") |
| 200 | |
| 201 | speedup = sqlite_time / tsv_time |
| 202 | print(f" → TSV is {speedup:.1f}x {'faster' if speedup > 1 else 'slower'}") |
| 203 | |
| 204 | results.append({ |
| 205 | "operation": f"Insert {size}", |
| 206 | "tsv": tsv_time, |
| 207 | "sqlite": sqlite_time, |
| 208 | "speedup": speedup |
| 209 | }) |
| 210 | |
| 211 | # Cleanup |
| 212 | tsv_insert.cleanup() |
| 213 | sqlite_insert.cleanup() |
| 214 | |
| 215 | # Query benchmarks |
| 216 | print("\nQuery Performance (100 queries):") |
| 217 | |
| 218 | tsv_query = TSVQueryBenchmark(size, 100) |
| 219 | tsv_time = tsv_query.run() |
| 220 | print(f" TSV: {format_time(tsv_time)} ({100/tsv_time:,.0f} queries/sec)") |
| 221 | |
| 222 | sqlite_query = SQLiteQueryBenchmark(size, 100) |
| 223 | sqlite_time = sqlite_query.run() |
| 224 | print(f" SQLite: {format_time(sqlite_time)} ({100/sqlite_time:,.0f} queries/sec)") |
| 225 | |
| 226 | speedup = sqlite_time / tsv_time |
| 227 | print(f" → TSV is {speedup:.1f}x {'faster' if speedup > 1 else 'slower'}") |
| 228 | |
| 229 | results.append({ |
| 230 | "operation": f"Query {size}", |
| 231 | "tsv": tsv_time, |
| 232 | "sqlite": sqlite_time, |
| 233 | "speedup": speedup |
| 234 | }) |
| 235 | |
| 236 | # Cleanup |
| 237 | tsv_query.cleanup() |
| 238 | sqlite_query.cleanup() |
| 239 | |
| 240 | # Summary |
| 241 | print("\n" + "=" * 70) |
| 242 | print("SUMMARY") |
| 243 | print("=" * 70) |
| 244 | |
| 245 | print("\n| Operation | TSV Time | SQLite Time | Speedup |") |
| 246 | print("|----------------|----------|-------------|---------|") |
| 247 | |
| 248 | for r in results: |
| 249 | op = r["operation"].ljust(14) |
| 250 | tsv = format_time(r["tsv"]).rjust(8) |
| 251 | sqlite = format_time(r["sqlite"]).rjust(11) |
| 252 | speedup = f"{r['speedup']:.1f}x".rjust(7) |
| 253 | print(f"| {op} | {tsv} | {sqlite} | {speedup} |") |
| 254 | |
| 255 | print("\n📝 Notes:") |
| 256 | print("- TSV excels at batch inserts due to append-only design") |
| 257 | print("- SQLite better for complex queries and joins") |
| 258 | print("- TSV files are human-readable and Git-friendly") |
| 259 | print("- SQLite requires no server but needs SQL knowledge") |
| 260 | print("- TSV has zero dependencies, SQLite is built-in to Python") |
| 261 | |
| 262 | # File size comparison |
| 263 | print("\n💾 Storage Comparison (10,000 records):") |
| 264 | |
| 265 | # Create TSV with data |
| 266 | tsv_test = TSV("size_test", ["id", "name", "email", "age", "created"]) |
| 267 | records = [ |
| 268 | {"id": str(i), "name": f"User_{i}", "email": f"user{i}@example.com", |
| 269 | "age": str(20 + (i % 50)), "created": str(time.time())} |
| 270 | for i in range(10000) |
| 271 | ] |
| 272 | tsv_test.insert_many(records) |
| 273 | |
| 274 | tsv_size = os.path.getsize(tsv_test.data_file) |
| 275 | idx_size = os.path.getsize(tsv_test.index_file) if tsv_test.index_file.exists() else 0 |
| 276 | tsv_total = tsv_size + idx_size |
| 277 | |
| 278 | print(f" TSV: {tsv_size:,} bytes (data) + {idx_size:,} bytes (index) = {tsv_total:,} bytes") |
| 279 | |
| 280 | # Create SQLite with same data |
| 281 | db_file = tempfile.NamedTemporaryFile(suffix=".db", delete=False) |
| 282 | conn = sqlite3.connect(db_file.name) |
| 283 | conn.execute(""" |
| 284 | CREATE TABLE size_test ( |
| 285 | id TEXT PRIMARY KEY, |
| 286 | name TEXT, |
| 287 | email TEXT, |
| 288 | age INTEGER, |
| 289 | created REAL |
| 290 | ) |
| 291 | """) |
| 292 | sqlite_records = [ |
| 293 | (str(i), f"User_{i}", f"user{i}@example.com", 20 + (i % 50), time.time()) |
| 294 | for i in range(10000) |
| 295 | ] |
| 296 | conn.executemany("INSERT INTO size_test VALUES (?, ?, ?, ?, ?)", sqlite_records) |
| 297 | conn.commit() |
| 298 | conn.close() |
| 299 | |
| 300 | sqlite_size = os.path.getsize(db_file.name) |
| 301 | print(f" SQLite: {sqlite_size:,} bytes") |
| 302 | |
| 303 | ratio = tsv_total / sqlite_size |
| 304 | print(f" → TSV uses {ratio:.1f}x {'more' if ratio > 1 else 'less'} space") |
| 305 | |
| 306 | # Cleanup |
| 307 | tsv_test.drop() |
| 308 | os.unlink(db_file.name) |
| 309 | |
| 310 | print("\n✅ Benchmark complete!") |
| 311 | |
| 312 | |
| 313 | if __name__ == "__main__": |
| 314 | run_comparison() |