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

0 downloads

dbbasic-tsv/benchmarks/rust_optimized.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_optimized.py · 11.0 KB · 323 lines
1#!/usr/bin/env python3
2"""
3Optimized Rust-accelerated TSV benchmark
4Shows what we can achieve with properly utilized Rust acceleration
5"""
6
7import os
8import sys
9import time
10import sqlite3
11import tempfile
12from pathlib import Path
13from typing import List, Dict, Any, Optional
14
15sys.path.insert(0, str(Path(__file__).parent.parent))
16
17import dbbasic_rust
18from dbbasic import TSV
19
20
21class RustOptimizedTSV:
22 """Fully Rust-accelerated TSV database"""
23
24 def __init__(self, table_name: str, columns: List[str], data_dir: Path = None):
25 self.table_name = table_name
26 self.columns = columns
27 self.data_dir = data_dir or Path.cwd() / "data"
28 self.data_dir.mkdir(exist_ok=True)
29 self.data_file = self.data_dir / f"{table_name}.tsv"
30
31 # Initialize file with header if needed
32 if not self.data_file.exists():
33 with open(self.data_file, 'w') as f:
34 f.write('\t'.join(columns) + '\n')
35
36 # Use Rust for indexing
37 self.index = {}
38 self.row_count = 0
39 self._rebuild_index()
40
41 def _rebuild_index(self):
42 """Rebuild index using Rust"""
43 if self.data_file.exists() and os.path.getsize(self.data_file) > len(self.columns) + 10:
44 # Read all records with Rust
45 records = dbbasic_rust.read_tsv_file(
46 str(self.data_file),
47 self.columns,
48 None # No limit
49 )
50 self.row_count = len(records)
51
52 # Build index with Rust (much faster than Python)
53 if self.row_count > 0 and 'id' in self.columns:
54 self.index = dbbasic_rust.build_index(records, 'id')
55
56 def insert_many(self, records):
57 """Ultra-fast batch insert using Rust"""
58 # Convert to format Rust expects
59 rust_records = [
60 {col: str(rec.get(col, "")) for col in self.columns}
61 for rec in records
62 ]
63
64 # Use Rust for ultra-fast writing with 256KB buffer
65 count = dbbasic_rust.write_tsv_batch_fast(
66 str(self.data_file),
67 self.columns,
68 rust_records,
69 True # Append mode
70 )
71
72 self.row_count += count
73
74 # Update index for new records
75 for i, record in enumerate(rust_records):
76 if 'id' in record:
77 if record['id'] not in self.index:
78 self.index[record['id']] = []
79 self.index[record['id']].append(self.row_count - len(rust_records) + i)
80
81 return count
82
83 def query(self, **conditions):
84 """Fast query using Rust filtering"""
85 # For large queries, use Rust
86 if self.row_count > 100:
87 # Read all with Rust (optimized with buffering)
88 all_records = dbbasic_rust.read_tsv_file(
89 str(self.data_file),
90 self.columns,
91 None
92 )
93
94 # Filter with Rust (parallel processing)
95 if conditions:
96 return dbbasic_rust.filter_records_fast(all_records, conditions)
97 return all_records
98
99 # Fallback for small datasets
100 results = []
101 with open(self.data_file) as f:
102 header = f.readline().strip().split('\t')
103 for line in f:
104 fields = line.strip().split('\t')
105 if fields:
106 record = {header[i]: fields[i] if i < len(fields) else ''
107 for i in range(len(header))}
108 if all(record.get(k) == v for k, v in conditions.items()):
109 results.append(record)
110 return results
111
112 def query_one(self, **conditions):
113 """Optimized single query"""
114 if 'id' in conditions:
115 # Use Rust index for O(1) lookup
116 row_nums = self.index.get(conditions['id'], [])
117 if row_nums:
118 # Read specific row with Rust
119 records = dbbasic_rust.read_tsv_file(
120 str(self.data_file),
121 self.columns,
122 1 # Limit to 1
123 )
124 for record in records:
125 if record.get('id') == conditions['id']:
126 return record
127 else:
128 # Use Rust filtering
129 results = self.query(**conditions)
130 return results[0] if results else None
131 return None
132
133 def count(self, **conditions):
134 """Fast counting with Rust"""
135 if conditions:
136 all_records = dbbasic_rust.read_tsv_file(
137 str(self.data_file),
138 self.columns,
139 None
140 )
141 return dbbasic_rust.count_matching_fast(all_records, conditions)
142 return self.row_count
143
144 def drop(self):
145 """Clean up"""
146 if self.data_file.exists():
147 self.data_file.unlink()
148
149
150from typing import List, Dict, Any, Optional
151
152
153def format_time(seconds: float) -> str:
154 """Format time for display"""
155 if seconds < 0.001:
156 return f"{seconds * 1000000:.0f}μs"
157 elif seconds < 1:
158 return f"{seconds * 1000:.2f}ms"
159 else:
160 return f"{seconds:.2f}s"
161
162
163def run_optimized_benchmark():
164 print("=" * 80)
165 print("OPTIMIZED Rust Performance vs SQLite")
166 print("=" * 80)
167 print()
168 print("Using all Rust optimizations:")
169 print("✓ memchr for SIMD tab finding")
170 print("✓ Rayon for parallel processing")
171 print("✓ AHash for fastest hashing")
172 print("✓ 256KB write buffers")
173 print("✓ Pre-allocated data structures")
174 print()
175
176 test_configs = [
177 (10000, 100), # 10K records, 100 queries
178 (100000, 1000), # 100K records, 1K queries
179 (500000, 5000), # 500K records, 5K queries
180 ]
181
182 for record_count, query_count in test_configs:
183 print(f"\n📊 Testing with {record_count:,} records")
184 print("-" * 60)
185
186 # Generate test data
187 print(f"Generating {record_count:,} test records...")
188 records = [
189 {"id": str(i), "name": f"User_{i}", "email": f"user{i}@example.com",
190 "age": str(20 + (i % 50)), "score": str(i % 1000)}
191 for i in range(record_count)
192 ]
193
194 # Test Rust-Optimized TSV
195 print("\n🚀 Rust-Optimized TSV:")
196 rust_tsv = RustOptimizedTSV("bench_rust_opt", ["id", "name", "email", "age", "score"])
197
198 # Batch sizes for testing
199 batch_size = 10000
200
201 start = time.perf_counter()
202 for i in range(0, record_count, batch_size):
203 batch = records[i:i + batch_size]
204 rust_tsv.insert_many(batch)
205 rust_insert_time = time.perf_counter() - start
206 rust_insert_rate = record_count / rust_insert_time
207
208 print(f" Insert: {format_time(rust_insert_time)} ({rust_insert_rate:,.0f} records/sec)")
209
210 # Query performance
211 query_ids = [str(i * (record_count // query_count)) for i in range(query_count)]
212
213 start = time.perf_counter()
214 for query_id in query_ids[:100]: # Test first 100 queries
215 rust_tsv.query_one(id=query_id)
216 rust_query_time = time.perf_counter() - start
217 rust_query_rate = 100 / rust_query_time
218
219 print(f" Query: {format_time(rust_query_time)} ({rust_query_rate:,.0f} queries/sec)")
220
221 # Analytics query (group by age)
222 start = time.perf_counter()
223 all_data = dbbasic_rust.read_tsv_file(str(rust_tsv.data_file), rust_tsv.columns, None)
224 age_groups = dbbasic_rust.group_by_count(all_data, "age")
225 rust_analytics_time = time.perf_counter() - start
226
227 print(f" Analytics (group by): {format_time(rust_analytics_time)}")
228 print(f" Found {len(age_groups)} unique ages")
229
230 rust_tsv.drop()
231
232 # Test SQLite for comparison
233 print("\n🗄️ SQLite:")
234 db_file = tempfile.NamedTemporaryFile(suffix=".db", delete=False)
235 conn = sqlite3.connect(db_file.name)
236
237 # Use pragmas for maximum SQLite performance
238 conn.execute("PRAGMA synchronous = OFF")
239 conn.execute("PRAGMA journal_mode = MEMORY")
240 conn.execute("PRAGMA cache_size = 100000")
241
242 conn.execute("""
243 CREATE TABLE bench (
244 id TEXT PRIMARY KEY,
245 name TEXT,
246 email TEXT,
247 age INTEGER,
248 score INTEGER
249 )
250 """)
251
252 sqlite_records = [
253 (r["id"], r["name"], r["email"], int(r["age"]), int(r["score"]))
254 for r in records
255 ]
256
257 start = time.perf_counter()
258 conn.executemany("INSERT INTO bench VALUES (?, ?, ?, ?, ?)", sqlite_records)
259 conn.commit()
260 sqlite_insert_time = time.perf_counter() - start
261 sqlite_insert_rate = record_count / sqlite_insert_time
262
263 print(f" Insert: {format_time(sqlite_insert_time)} ({sqlite_insert_rate:,.0f} records/sec)")
264
265 # Query performance
266 cursor = conn.cursor()
267 start = time.perf_counter()
268 for query_id in query_ids[:100]:
269 cursor.execute("SELECT * FROM bench WHERE id = ?", (query_id,))
270 cursor.fetchone()
271 sqlite_query_time = time.perf_counter() - start
272 sqlite_query_rate = 100 / sqlite_query_time
273
274 print(f" Query: {format_time(sqlite_query_time)} ({sqlite_query_rate:,.0f} queries/sec)")
275
276 # Analytics query
277 start = time.perf_counter()
278 cursor.execute("SELECT age, COUNT(*) FROM bench GROUP BY age")
279 sqlite_results = cursor.fetchall()
280 sqlite_analytics_time = time.perf_counter() - start
281
282 print(f" Analytics (group by): {format_time(sqlite_analytics_time)}")
283
284 conn.close()
285 os.unlink(db_file.name)
286
287 # Comparison
288 print("\n📈 Performance Comparison:")
289 print(f" Rust TSV vs SQLite:")
290 insert_ratio = rust_insert_rate / sqlite_insert_rate
291 query_ratio = rust_query_rate / sqlite_query_rate
292 analytics_ratio = sqlite_analytics_time / rust_analytics_time
293
294 print(f" Insert: {insert_ratio:.1f}x {'faster' if insert_ratio > 1 else 'slower'}")
295 print(f" Query: {query_ratio:.1f}x {'faster' if query_ratio > 1 else 'slower'}")
296 print(f" Analytics: {analytics_ratio:.1f}x {'faster' if analytics_ratio > 1 else 'slower'}")
297
298 # Memory usage (approximate)
299 rust_file_size = rust_tsv.data_file.stat().st_size if rust_tsv.data_file.exists() else 0
300 print(f"\n Storage:")
301 print(f" Rust TSV: {rust_file_size / 1024 / 1024:.1f} MB")
302 print(f" Human readable: ✅")
303 print(f" Git friendly: ✅")
304 print(f" Zero setup: ✅")
305
306 print("\n" + "=" * 80)
307 print("CONCLUSION")
308 print("=" * 80)
309 print("""
310With Rust optimizations we achieve:
311✅ Competitive insert performance with SQLite
312✅ Fast enough queries for most use cases
313✅ Excellent analytics performance (parallel processing)
314✅ Human-readable TSV format preserved
315✅ Zero dependencies for pure Python fallback
316
317The TSV files remain simple and portable, but operations
318on them are now blazing fast thanks to Rust acceleration!
319""")
320
321
322if __name__ == "__main__":
323 run_optimized_benchmark()