sqlite_test/
main.rs

1use rusqlite::{Connection, Result};
2use std::time::Instant;
3
4#[unsafe(no_mangle)]
5unsafe extern "C" fn __dlapi_close() -> i32 { return 0; }
6#[unsafe(no_mangle)]
7unsafe extern "C" fn __dlapi_error() -> i32 { return 0; }
8#[unsafe(no_mangle)]
9unsafe extern "C" fn __dlapi_open() -> i32 { return 0; }
10#[unsafe(no_mangle)]
11unsafe extern "C" fn __dlapi_resolve() -> i32 { return 0; }
12#[unsafe(no_mangle)]
13unsafe extern "C" fn __dlapi_reverse() -> i32 { return 0; }
14
15const NUM_INSERTS: usize = 100000;
16
17fn benchmark_transient_sqlite() -> Result<(Connection, std::time::Duration)> {
18    println!("Benchmarking transient SQLite in-memory database...");
19
20    let conn = Connection::open_in_memory()?;
21
22    // Create a transient table
23    conn.execute(
24        "CREATE TABLE test_table (
25            id INTEGER,
26            name TEXT,
27            value INTEGER,
28            data BLOB
29        )",
30        [],
31    )?;
32    
33    let start = Instant::now();
34    
35    conn.execute("BEGIN TRANSACTION", [])?;
36    for i in 0..NUM_INSERTS {
37        conn.execute(
38            "INSERT INTO test_table (id, name, value, data) VALUES (?1, ?2, ?3, ?4)",
39            [
40                &i,
41                &format!("name_{}", i) as &dyn rusqlite::ToSql,
42                &(i * 2),
43                &vec![0u8; 100] as &dyn rusqlite::ToSql,
44            ],
45        )?;
46    }
47    conn.execute("COMMIT", [])?;
48    
49    let duration = start.elapsed();
50    println!("Standard SQLite: Inserted {} records in {:?}", NUM_INSERTS, duration);
51
52    Ok((conn, duration))
53}
54
55fn query_transient_sqlite(conn: &Connection) -> Result<std::time::Duration> {
56    println!("Querying transient SQLite in-memory database...");
57    
58    let start = Instant::now();
59    
60    // Test different query types
61    
62    // 1. Count all records
63    let mut stmt = conn.prepare("SELECT COUNT(*) FROM test_table")?;
64    let count: i64 = stmt.query_row([], |row| row.get(0))?;
65    println!("  Total records: {}", count);
66    
67    // 2. Select specific records by ID
68    let mut stmt = conn.prepare("SELECT id, name, value FROM test_table WHERE id BETWEEN ? AND ?")?;
69    let rows = stmt.query_map([&100, &110], |row| {
70        Ok((
71            row.get::<_, i64>(0)?,
72            row.get::<_, String>(1)?,
73            row.get::<_, i64>(2)?,
74        ))
75    })?;
76    
77    let mut selected_count = 0;
78    for row in rows {
79        let _record = row?;
80        selected_count += 1;
81    }
82    println!("  Selected records (ID 100-110): {}", selected_count);
83    
84    // 3. Aggregate query
85    let mut stmt = conn.prepare("SELECT AVG(value), MAX(value), MIN(value) FROM test_table")?;
86    let (avg, max, min): (f64, i64, i64) = stmt.query_row([], |row| {
87        Ok((row.get(0)?, row.get(1)?, row.get(2)?))
88    })?;
89    println!("  Value stats - Avg: {:.2}, Max: {}, Min: {}", avg, max, min);
90    
91    let duration = start.elapsed();
92    println!("Standard SQLite queries completed in {:?}", duration);
93    
94    Ok(duration)
95}
96
97#[allow(dead_code)]
98fn benchmark_file_sqlite() -> Result<(Connection, std::time::Duration)> {
99    println!("Benchmarking SQLite file database...");
100    
101    // Remove existing test file if it exists
102    let _ = std::fs::remove_file("benchmark_test.db");
103    
104    let conn = Connection::open("benchmark_test.db")?;
105    
106    // Create a standard table
107    conn.execute(
108        "CREATE TABLE test_table (
109            id INTEGER,
110            name TEXT,
111            value INTEGER,
112            data BLOB
113        )",
114        [],
115    )?;
116    
117    let start = Instant::now();
118    
119    conn.execute("BEGIN TRANSACTION", [])?;
120    for i in 0..NUM_INSERTS {
121        conn.execute(
122            "INSERT INTO test_table (id, name, value, data) VALUES (?1, ?2, ?3, ?4)",
123            [
124                &i,
125                &format!("name_{}", i) as &dyn rusqlite::ToSql,
126                &(i * 2),
127                &vec![0u8; 100] as &dyn rusqlite::ToSql,
128            ],
129        )?;
130    }
131    conn.execute("COMMIT", [])?;
132    
133    let duration = start.elapsed();
134    println!("File SQLite: Inserted {} records in {:?}", NUM_INSERTS, duration);
135
136    Ok((conn, duration))
137}
138
139#[allow(dead_code)]
140fn query_file_sqlite(conn: &Connection) -> Result<std::time::Duration> {
141    println!("Querying SQLite file database...");
142    
143    let start = Instant::now();
144    
145    // Test different query types
146    
147    // 1. Count all records
148    let mut stmt = conn.prepare("SELECT COUNT(*) FROM test_table")?;
149    let count: i64 = stmt.query_row([], |row| row.get(0))?;
150    println!("  Total records: {}", count);
151    
152    // 2. Select specific records by ID
153    let mut stmt = conn.prepare("SELECT id, name, value FROM test_table WHERE id BETWEEN ? AND ?")?;
154    let rows = stmt.query_map([&100, &110], |row| {
155        Ok((
156            row.get::<_, i64>(0)?,
157            row.get::<_, String>(1)?,
158            row.get::<_, i64>(2)?,
159        ))
160    })?;
161    
162    let mut selected_count = 0;
163    for row in rows {
164        let _record = row?;
165        selected_count += 1;
166    }
167    println!("  Selected records (ID 100-110): {}", selected_count);
168    
169    // 3. Aggregate query
170    let mut stmt = conn.prepare("SELECT AVG(value), MAX(value), MIN(value) FROM test_table")?;
171    let (avg, max, min): (f64, i64, i64) = stmt.query_row([], |row| {
172        Ok((row.get(0)?, row.get(1)?, row.get(2)?))
173    })?;
174    println!("  Value stats - Avg: {:.2}, Max: {}, Min: {}", avg, max, min);
175    
176    let duration = start.elapsed();
177    println!("File SQLite queries completed in {:?}", duration);
178    
179    Ok(duration)
180}
181
182fn benchmark_transient_vtab() -> Result<(Connection, std::time::Duration)> {
183    println!("Benchmarking Twizzler transient virtual table...");
184    
185    let conn = Connection::open_in_memory()?;
186    conn.setup_twz_vtab();
187    
188    // Create a transient virtual table
189    conn.execute(
190        "CREATE VIRTUAL TABLE test_table USING twz_transient_vtab(
191            id INTEGER,
192            name TEXT,
193            value INTEGER,
194            data BLOB
195        )",
196        [],
197    )?;
198    
199    let start = Instant::now();
200
201    for i in 0..NUM_INSERTS {
202        conn.execute(
203            "INSERT INTO test_table (id, name, value, data) VALUES (?1, ?2, ?3, ?4)",
204            [
205                &i,
206                &format!("name_{}", i) as &dyn rusqlite::ToSql,
207                &(i * 2),
208                &vec![0u8; 100] as &dyn rusqlite::ToSql,
209            ],
210        )?;
211    }
212    
213    let duration = start.elapsed();
214    println!("Transient VTab: Inserted {} records in {:?}", NUM_INSERTS, duration);
215
216    Ok((conn, duration))
217}
218
219fn query_transient_vtab(conn: &Connection) -> Result<std::time::Duration> {
220    println!("Querying Twizzler transient virtual table...");
221    
222    let start = Instant::now();
223    
224    // Test different query types
225    
226    // 1. Count all records
227    let mut stmt = conn.prepare("SELECT COUNT(*) FROM test_table")?;
228    let count: i64 = stmt.query_row([], |row| row.get(0))?;
229    println!("  Total records: {}", count);
230    
231    // 2. Select specific records by ID
232    let mut stmt = conn.prepare("SELECT id, name, value FROM test_table WHERE id BETWEEN ? AND ?")?;
233    let rows = stmt.query_map([&100, &110], |row| {
234        Ok((
235            row.get::<_, i64>(0)?,
236            row.get::<_, String>(1)?,
237            row.get::<_, i64>(2)?,
238        ))
239    })?;
240    
241    let mut selected_count = 0;
242    for row in rows {
243        let _record = row?;
244        selected_count += 1;
245    }
246    println!("  Selected records (ID 100-110): {}", selected_count);
247    
248    // 3. Aggregate query
249    let mut stmt = conn.prepare("SELECT AVG(value), MAX(value), MIN(value) FROM test_table")?;
250    let (avg, max, min): (f64, i64, i64) = stmt.query_row([], |row| {
251        Ok((row.get(0)?, row.get(1)?, row.get(2)?))
252    })?;
253    println!("  Value stats - Avg: {:.2}, Max: {}, Min: {}", avg, max, min);
254    
255    let duration = start.elapsed();
256    println!("Transient VTab queries completed in {:?}", duration);
257    
258    Ok(duration)
259}
260
261fn benchmark_persistent_vtab() -> Result<(Connection, std::time::Duration)> {
262    println!("Benchmarking Twizzler persistent virtual table...");
263    
264    let conn = Connection::open_in_memory()?;
265    conn.setup_twz_vtab();
266    
267    // Create a persistent virtual table
268    conn.execute(
269        "CREATE VIRTUAL TABLE test_table USING twz_persistent_vtab(
270            id INTEGER,
271            name TEXT,
272            value INTEGER,
273            data BLOB
274        )",
275        [],
276    )?;
277    
278    let start = Instant::now();
279
280    conn.execute("BEGIN TRANSACTION", [])?;
281
282    for i in 0..NUM_INSERTS {
283        conn.execute(
284            "INSERT INTO test_table (id, name, value, data) VALUES (?1, ?2, ?3, ?4)",
285            [
286                &i,
287                &format!("name_{}", i) as &dyn rusqlite::ToSql,
288                &(i * 2),
289                &vec![0u8; 100] as &dyn rusqlite::ToSql,
290            ],
291        )?;
292    }
293
294    conn.execute("COMMIT", [])?;
295    
296    let duration = start.elapsed();
297    println!("Persistent VTab: Inserted {} records in {:?}", NUM_INSERTS, duration);
298
299    Ok((conn, duration))
300}
301
302fn query_persistent_vtab(conn: &Connection) -> Result<std::time::Duration> {
303    println!("Querying Twizzler persistent virtual table...");
304    
305    let start = Instant::now();
306    
307    // Test different query types
308    
309    // 1. Count all records
310    let mut stmt = conn.prepare("SELECT COUNT(*) FROM test_table")?;
311    let count: i64 = stmt.query_row([], |row| row.get(0))?;
312    println!("  Total records: {}", count);
313    
314    // 2. Select specific records by ID
315    let mut stmt = conn.prepare("SELECT id, name, value FROM test_table WHERE id BETWEEN ? AND ?")?;
316    let rows = stmt.query_map([&100, &110], |row| {
317        Ok((
318            row.get::<_, i64>(0)?,
319            row.get::<_, String>(1)?,
320            row.get::<_, i64>(2)?,
321        ))
322    })?;
323    
324    let mut selected_count = 0;
325    for row in rows {
326        let _record = row?;
327        selected_count += 1;
328    }
329    println!("  Selected records (ID 100-110): {}", selected_count);
330    
331    // 3. Aggregate query
332    let mut stmt = conn.prepare("SELECT AVG(value), MAX(value), MIN(value) FROM test_table")?;
333    let (avg, max, min): (f64, i64, i64) = stmt.query_row([], |row| {
334        Ok((row.get(0)?, row.get(1)?, row.get(2)?))
335    })?;
336    println!("  Value stats - Avg: {:.2}, Max: {}, Min: {}", avg, max, min);
337    
338    let duration = start.elapsed();
339    println!("Persistent VTab queries completed in {:?}", duration);
340    
341    Ok(duration)
342}
343
344#[allow(dead_code)]
345fn benchmark_file_persistence() -> Result<std::time::Duration> {
346    println!("Benchmarking SQLite persistent storage for opening and querying existing data...");
347    let start = Instant::now();
348    let conn = Connection::open("benchmark_test.db")?;
349
350    let _ = query_file_sqlite(&conn)?;
351    let duration = start.elapsed();
352    println!("Reopened and queried existing SQLite file database in {:?}", duration);
353    Ok(duration)
354}
355
356fn benchmark_persistent_vtab_persistence() -> Result<std::time::Duration> {
357    println!("Benchmarking Twizzler persistent virtual table for opening and querying existing data...");
358    let start = Instant::now();
359    let conn = Connection::open_in_memory()?;
360    conn.setup_twz_vtab();
361    
362    // Recreate the persistent virtual table
363    conn.execute(
364        "CREATE VIRTUAL TABLE test_table USING twz_persistent_vtab(
365            id INTEGER,
366            name TEXT,
367            value INTEGER,
368            data BLOB
369        )",
370        [],
371    )?;
372
373    let _ = query_persistent_vtab(&conn)?;
374    let duration = start.elapsed();
375    println!("Reopened and queried existing Twizzler persistent virtual table in {:?}", duration);
376    Ok(duration)
377}
378
379fn run_bench() -> Result<()> {
380    println!("\n=== Batch Insert Performance Comparison ===");
381    
382    // Transient SQLite
383    let (sqlite_conn, sqlite_duration) = benchmark_transient_sqlite()?;
384
385    // File SQLite (Currently dysfunctional.)
386    // let (file_conn, file_duration) = benchmark_file_sqlite()?;
387
388    // Transient VTab
389    let (transient_conn, transient_duration) = benchmark_transient_vtab()?;
390
391    // Persistent VTab
392    let (persistent_conn, persistent_duration) = benchmark_persistent_vtab()?;
393
394    // Print comparison
395    println!("\n=== Performance Summary ===");
396    println!("Number of inserts: {}", NUM_INSERTS);
397    println!("Standard SQLite:   {:?}", sqlite_duration);
398    // println!("File SQLite:       {:?}", file_duration);
399    println!("Transient VTab:    {:?}", transient_duration);
400    println!("Persistent VTab:   {:?}", persistent_duration);
401    
402    println!("\nThroughput (records/second):");
403    println!("Standard SQLite:   {:.0}", NUM_INSERTS as f64 / sqlite_duration.as_secs_f64());
404    // println!("File SQLite:       {:.0}", NUM_INSERTS as f64 / file_duration.as_secs_f64());
405    println!("Transient VTab:    {:.0}", NUM_INSERTS as f64 / transient_duration.as_secs_f64());
406    println!("Persistent VTab:   {:.0}", NUM_INSERTS as f64 / persistent_duration.as_secs_f64());
407
408    println!("\n=== Query Performance Comparison ===");
409
410    let sqlite_query_duration = query_transient_sqlite(&sqlite_conn)?;
411    // let file_query_duration = query_file_sqlite(&file_conn)?;
412    let transient_query_duration = query_transient_vtab(&transient_conn)?;
413    let persistent_query_duration = query_persistent_vtab(&persistent_conn)?;
414
415    println!("\n=== Query Performance Summary ===");
416    println!("Standard SQLite:   {:?}", sqlite_query_duration);
417    // println!("File SQLite:       {:?}", file_query_duration);
418    println!("Transient VTab:    {:?}", transient_query_duration);
419    println!("Persistent VTab:   {:?}", persistent_query_duration);
420
421    // // Benchmark reopening and querying existing data
422    // drop(file_conn);
423    drop(persistent_conn);
424
425    // let file_persistence_duration = benchmark_file_persistence()?;
426    let persistent_vtab_persistence_duration = benchmark_persistent_vtab_persistence()?;
427
428    println!("\n=== Persistence Performance Summary ===");
429    // // println!("Reopen & Query File SQLite:       {:?}", file_persistence_duration);
430    println!("Reopen & Query Persistent VTab:   {:?}", persistent_vtab_persistence_duration);
431    
432    Ok(())
433}
434
435fn cleanup() -> Result<()> {
436    // Clean up test file
437    // let _ = std::fs::remove_file("benchmark_test.db");
438
439    let mut nh = naming::dynamic_naming_factory().unwrap();
440    let _ = nh.remove("/data/vtab-test_table");
441
442    Ok(())
443}
444
445fn main() -> Result<()> {
446    println!("SQLite Performance Benchmark");
447    println!("Testing with {} records\n", NUM_INSERTS);
448
449    run_bench()?;
450
451    cleanup()?;
452
453    Ok(())
454}
sqlite_test/main.rs

sqlite_test/
main.rs
