Skip to content

MOBILE_BENCHMARK.md

Latest commit

 

History

History
330 lines (248 loc) · 12.5 KB

MOBILE_BENCHMARK.md

File metadata and controls

330 lines (248 loc) · 12.5 KB

Mobile Performance Benchmarks

Last Updated: October 24, 2025
Platforms: iOS (iPhone 16 Simulator, iOS 18.4), Android (test_avd, Android 13, ARM64)

Benchmark Scope

AbsurderSQL performance is compared against two popular React Native SQLite libraries:

  1. react-native-sqlite-storage - Bare React Native SQLite wrapper
  2. WatermelonDB - Reactive ORM with lazy loading

Excluded from Comparison

expo-sqlite - Requires Expo managed workflow infrastructure (expo-modules-core gradle plugin, expo-asset, expo-file-system). This project uses bare React Native for maximum flexibility and minimal dependencies. Adding Expo infrastructure would require:

  • Converting to Expo managed workflow OR
  • Installing expo-modules-core gradle plugin (adds ~15MB to APK)
  • Configuring Expo autolinking and module resolution
  • Managing Expo SDK version compatibility

Since AbsurderSQL targets bare React Native projects, expo-sqlite is out of scope. Developers using Expo can still use AbsurderSQL's native modules directly.

Android Results

vs react-native-sqlite-storage

Test Environment:

  • Platform: Android Emulator (test_avd, Android 13, ARM64)
  • Date: October 24, 2025
  • Methodology: 4 consecutive runs, averaged results

Benchmark Results:

Test AbsurderSQL (avg) react-native-sqlite-storage (avg) Speedup
1000 INSERTs (transaction) ~362ms ~2392ms 6.61x faster
5000 INSERTs (transaction w/ executeBatch) ~39ms ~354ms 9.08x faster
100 SELECTs (PreparedStatement) ~38ms ~83ms 2.18x faster
Stream 5000 rows (batch 100) ~52ms ~100ms 1.93x faster
Complex JOIN (5K+ records) ~15ms ~55ms 3.67x faster

Key Finding: AbsurderSQL's executeBatch() API provides 9.08x performance advantage on bulk INSERT operations by reducing React Native bridge overhead from 5000 calls to 1 call.

Detailed Results (4 runs):

Run 1:

  • 1000 INSERTs: 362ms vs 2475ms = 6.84x faster ⭐
  • 5000 INSERTs: 39ms vs 350ms = 8.97x faster ⭐
  • 100 SELECTs (PreparedStatement): 39ms vs 95ms = 2.44x faster ⭐
  • Stream 5000 rows: 52ms vs 100ms = 1.93x faster ⭐
  • Complex JOIN: 15ms vs 55ms = 3.67x faster ⭐

Run 2:

  • 1000 INSERTs: 363ms vs 2381ms = 6.56x faster
  • 5000 INSERTs: 37ms vs 345ms = 9.32x faster
  • 100 SELECTs (PreparedStatement): 37ms vs 80ms = 2.16x faster
  • Stream 5000 rows: 50ms vs 95ms = 2.10x faster
  • Complex JOIN: 15ms vs 57ms = 3.80x faster

Run 3:

  • 1000 INSERTs: 358ms vs 2293ms = 6.41x faster
  • 5000 INSERTs: 39ms vs 339ms = 8.69x faster
  • 100 SELECTs (PreparedStatement): 38ms vs 75ms = 1.97x faster
  • Stream 5000 rows: 48ms vs 92ms = 2.66x faster
  • Complex JOIN: 13ms vs 55ms = 4.23x faster

Run 4:

  • 1000 INSERTs: 363ms vs 2460ms = 6.78x faster
  • 5000 INSERTs: 40ms vs 382ms = 9.55x faster
  • 100 SELECTs (PreparedStatement): 38ms vs 75ms = 1.97x faster
  • Stream 5000 rows: 53ms vs 96ms = 2.20x faster
  • Complex JOIN: 17ms vs 54ms = 3.18x faster

Consistency: Performance advantage is stable across all 4 runs, demonstrating reliable superiority in INSERT and JOIN operations. SELECT performance is competitive but not dominant.

Technical Advantages:

  1. Batch Execution API: Single bridge call for N SQL statements
  2. Native Performance: Rust implementation with zero-copy optimization
  3. Efficient Serialization: Direct JSON serialization without intermediate conversions
  4. Transaction Optimization: executeBatch + transaction = maximum throughput
  5. Streaming API: Built-in cursor-based streaming with automatic cleanup

Streaming API Performance (Android):

AbsurderSQL provides a native streaming API for memory-efficient processing of large result sets:

Test Stream Time Execute Time Memory Usage
5000 rows (batch 100) 52ms 12ms 11.4KB vs 5,680KB (498x savings)
50000 rows (batch 100) 527ms 66ms 11.4KB vs 5,680KB (498x savings)

Key Findings:

  • Memory Efficiency: Streaming uses constant memory (11.4KB) regardless of result set size
  • Speed Trade-off: Streaming is 4-8x slower due to multiple queries (LIMIT/OFFSET overhead)
  • Use Cases:
    • Large datasets (50K+ rows) where memory is constrained
    • Incremental processing (display rows as they arrive)
    • Early break scenarios (don't need all rows)
    • Small datasets (<10K rows) where speed matters more than memory

vs react-native-sqlite-storage:

  • AbsurderSQL: Native executeStream() API with automatic cursor management
  • react-native-sqlite-storage: Manual LIMIT/OFFSET pagination required
  • Performance: AbsurderSQL 1.93-2.66x faster on streaming (cleaner implementation, less overhead)

vs WatermelonDB:

  • WatermelonDB: No streaming support (ORM limitations)
  • AbsurderSQL is the only library with true cursor-based streaming

vs WatermelonDB

Test Environment:

  • Platform: Android Emulator (test_avd, Android 13, ARM64)
  • Date: October 24, 2025
  • WatermelonDB Version: @nozbe/watermelondb@0.25.5
  • Methodology: 4 consecutive runs, averaged results

Benchmark Results:

Test AbsurderSQL (avg) WatermelonDB (avg) Winner Speedup
1000 INSERTs (individual) 362ms 442ms AbsurderSQL 1.22x faster
5000 INSERTs (batch) 39ms 86ms AbsurderSQL 2.21x faster
100 SELECTs (PreparedStatement) 38ms 23ms WatermelonDB 1.65x slower
Complex JOIN (5K users, 20K orders) 15ms 955ms AbsurderSQL 63.67x faster

Detailed Results (4 runs):

Run 1:

  • 1000 INSERTs: 362ms vs 434ms = 1.20x faster ⭐
  • 5000 INSERTs: 39ms vs 97ms = 2.49x faster ⭐
  • 100 SELECTs (PreparedStatement): 39ms vs 25ms = 0.64x (WatermelonDB wins) ⚠️
  • Complex JOIN: 15ms vs 967ms = 64.47x faster ⭐⭐⭐

Run 2:

  • 1000 INSERTs: 363ms vs 483ms = 1.33x faster
  • 5000 INSERTs: 37ms vs 80ms = 2.16x faster
  • 100 SELECTs (PreparedStatement): 37ms vs 20ms = 0.54x (WatermelonDB wins) ⚠️
  • Complex JOIN: 15ms vs 942ms = 62.80x faster ⭐⭐⭐

Run 3:

  • 1000 INSERTs: 358ms vs 446ms = 1.25x faster
  • 5000 INSERTs: 39ms vs 89ms = 2.28x faster
  • 100 SELECTs (PreparedStatement): 38ms vs 23ms = 0.61x (WatermelonDB wins) ⚠️
  • Complex JOIN: 13ms vs 967ms = 74.38x faster ⭐⭐⭐

Run 4:

  • 1000 INSERTs: 363ms vs 403ms = 1.11x faster
  • 5000 INSERTs: 40ms vs 76ms = 1.90x faster
  • 100 SELECTs (PreparedStatement): 38ms vs 22ms = 0.58x (WatermelonDB wins) ⚠️
  • Complex JOIN: 17ms vs 968ms = 56.94x faster ⭐⭐⭐

Key Observations:

  1. AbsurderSQL wins 3 of 4 tests - Dominant on INSERTs and JOINs
  2. WatermelonDB wins on SELECTs - 1.65x faster due to optimized query caching and lazy loading
  3. JOIN operations show massive 63.67x advantage - WatermelonDB's N+1 query problem is severe on Android
  4. Batch INSERTs 2.21x faster - AbsurderSQL's executeBatch API reduces bridge overhead
  5. Platform difference - Android shows smaller INSERT advantage (1.22x vs iOS 7.30x) but much larger JOIN advantage (63.67x vs iOS 2.08x)

iOS Results

vs react-native-sqlite-storage

Test Environment:

  • Platform: iOS Simulator (iPhone 16, iOS 18.4)
  • Date: October 22, 2025
  • Methodology: 4 consecutive runs, averaged results

Benchmark Results:

Test AbsurderSQL (avg) react-native-sqlite-storage (avg) Speedup
1000 INSERTs (transaction) ~374ms ~1630ms 4.36x faster
5000 INSERTs (transaction w/ executeBatch) ~43ms ~114ms 2.66x faster
100 SELECT queries ~38ms ~79ms 2.08x faster
Complex JOIN (5K+ records) ~12ms ~20ms 1.70x faster

Detailed Results (4 runs):

Run 1:

  • 1000 INSERTs: 3.47x faster
  • 5000 INSERTs: 3.11x faster
  • 100 SELECTs: 2.20x faster
  • Complex JOIN: 1.70x faster

Run 2:

  • 1000 INSERTs: 6.22x faster
  • 5000 INSERTs: 2.21x faster
  • 100 SELECTs: 1.71x faster
  • Complex JOIN: 1.70x faster

Run 3:

  • 1000 INSERTs: 3.69x faster
  • 5000 INSERTs: 2.42x faster
  • 100 SELECTs: 2.00x faster
  • Complex JOIN: 1.70x faster

Run 4:

  • 1000 INSERTs: 4.05x faster
  • 5000 INSERTs: 2.88x faster
  • 100 SELECTs: 2.40x faster
  • Complex JOIN: 1.70x faster

Platform Comparison:

iOS shows strong performance with executeBatch() delivering 2.66x advantage on bulk operations. While Android demonstrates higher peak performance (9.08x on 5000 INSERTs), iOS maintains consistent 2-4x performance gains across all operations with exceptional stability (Complex JOIN consistently 1.70x across all runs).

vs WatermelonDB

Test Environment:

  • Platform: iOS Simulator (iPhone 16, iOS 18.4)
  • Date: October 23, 2025
  • WatermelonDB Version: @nozbe/watermelondb@0.25.5
  • Methodology: 4 consecutive runs, averaged results

Benchmark Results:

Test AbsurderSQL (avg) WatermelonDB (avg) Speedup
1000 INSERTs (individual) 7.53ms 55ms 7.30x faster
5000 INSERTs (batch) 1.21ms 1.5ms 1.24x faster
100 SELECT queries 1.63ms 2.8ms 1.72x faster
Complex JOIN (5K users, 20K orders) 21.64ms 45ms 2.08x faster

Detailed Results (4 runs):

Run 1000 INSERTs 5000 INSERTs 100 SELECTs Complex JOIN
1 7.23ms 1.24ms 1.75ms 23ms
2 8.35ms 1.31ms 1.75ms 20.33ms
3 7.4ms 1.11ms 1.5ms 22.88ms
4 7.13ms 1.18ms 1.5ms 20.33ms

Key Observations:

  1. AbsurderSQL wins all 4 tests - 1.24x to 7.30x performance advantage
  2. Individual INSERTs show largest gap (7.30x) - WatermelonDB's ORM layer and reactive observables add significant overhead
  3. Batch operations are competitive (1.24x) - Both use bulk insert optimizations
  4. JOIN operations 2.08x faster - WatermelonDB lacks eager loading (GitHub Issue #763), requiring N+1 queries for related data
  5. Consistent overhead pattern - WatermelonDB's reactive layer adds ~1.5-2x overhead on most operations

Platform Comparison (iOS vs Android)

vs WatermelonDB

Test iOS Advantage Android Advantage
1000 INSERTs AbsurderSQL 7.30x AbsurderSQL 1.22x
5000 INSERTs AbsurderSQL 1.24x AbsurderSQL 2.21x
100 SELECTs AbsurderSQL 1.72x WatermelonDB 1.65x
Complex JOIN AbsurderSQL 2.08x AbsurderSQL 63.67x

Key Insights:

  • iOS: Stronger INSERT performance, consistent across all operations
  • Android: Massive JOIN advantage (63.67x), but WatermelonDB wins on SELECTs
  • Cross-platform consistency: AbsurderSQL maintains performance leadership on write-heavy workloads

Architecture Comparison

Feature AbsurderSQL WatermelonDB
SQL Execution Direct raw SQL ORM with Models/Query/Relation
Reactivity Manual Automatic observables
Schema Flexible, no migrations Strict schema with migrations
JOINs Single-query JOINs N+1 queries (no eager loading)
Learning Curve SQL knowledge WatermelonDB API + schema design
Use Case Performance-critical Reactive UI updates

Competitive Positioning

WatermelonDB

Strengths:

  • Reactive UI updates and automatic data synchronization with React components
  • Excellent SELECT performance on Android (query caching)
  • Observable queries for automatic UI updates
  • Developer-friendly ORM abstraction

Weaknesses:

  • 7.30x slower on individual INSERTs (iOS)
  • 63.67x slower on complex JOINs (Android) due to N+1 query problem
  • ORM overhead adds consistent 1.5-2x latency

When to use WatermelonDB:

  • Reactive UI updates are a priority
  • Simple SELECT queries dominate workload
  • ORM convenience outweighs performance needs

AbsurderSQL

Strengths:

  • Raw SQL performance with no ORM overhead
  • 6-9x faster on bulk INSERTs (executeBatch API)
  • 3-64x faster on complex JOINs
  • Flexible schema evolution without migrations
  • Simpler API for SQL-savvy developers

Weaknesses:

  • Manual reactivity (no automatic observables)
  • 1.65x slower on simple SELECTs (Android only)
  • Requires SQL knowledge

When to use AbsurderSQL:

  • Write-heavy workloads
  • Complex JOIN queries
  • Maximum performance is critical
  • SQL expertise available

Recommendation

  • Use WatermelonDB if: Reactive UI updates, simple SELECT queries, and ORM convenience are priorities
  • Use AbsurderSQL if: Write-heavy workloads, complex JOINs, or maximum performance are critical