A database in TSV text files: data you can read, fast inserts, and an optional Rust speed-up.

0 downloads

dbbasic-tsv/benchmarks/compare_sqlite.py
Download zip
Clone
Clone with git
git clone https://gitstable.com/git/askrobots/dbbasic-tsv.git
HTTPS
https://gitstable.com/git/askrobots/dbbasic-tsv.git
SSH
ssh://git@gitstable.com:2222/askrobots/dbbasic-tsv.git

Cloning needs no sign-in. Pushing over HTTPS asks for your username and an access token. SSH needs your key under SSH keys.

danq9 Add launch announcement for dbbasic-tsv v1.0.1 1c9b0b8 1 year, 1 month ago History
compare_sqlite.py · 9.3 KB · 314 lines
1#!/usr/bin/env python3
2"""
3Performance comparison: dbbasic-tsv vs SQLite
4Tests various operations and measures performance differences
5"""
6
7import os
8import sys
9import time
10import sqlite3
11import tempfile
12from pathlib import Path
13
14# Add parent to path for dbbasic import
15sys.path.insert(0, str(Path(__file__).parent.parent))
16from dbbasic import TSV
17
18
19class 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
39class 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
64class 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
98class 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
123class 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
164def 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
174def 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
313if __name__ == "__main__":
314 run_comparison()