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

0 downloads

dbbasic-tsv/benchmarks/rust_vs_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
rust_vs_sqlite.py · 8.0 KB · 240 lines
1#!/usr/bin/env python3
2"""
3Benchmark: Pure Python TSV vs Rust-Accelerated TSV vs SQLite
4Shows real performance with actual Rust acceleration
5"""
6
7import os
8import sys
9import time
10import sqlite3
11import tempfile
12from pathlib import Path
13
14sys.path.insert(0, str(Path(__file__).parent.parent))
15
16# Test if Rust is available
17try:
18 import dbbasic_rust
19 RUST_AVAILABLE = True
20 print("✅ Rust acceleration module loaded!")
21except ImportError:
22 RUST_AVAILABLE = False
23 print("❌ Rust module not available")
24
25from dbbasic import TSV
26
27
28class 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
80def 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
90def 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
218The 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
224This 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:
2331. cd rust
2342. maturin build --release
2353. pip install target/wheels/*.whl
236""")
237
238
239if __name__ == "__main__":
240 run_comparison()