SQLite Performance by Payload Size

Comprehensive Analysis of Operation Performance Across Different Data Sizes

Payload Size Categories

ExtraSmall
~100 bytes
Small
~1 KB
Medium
~10 KB
Large
~100 KB
ExtraLarge
~1 MB
Testing shows dramatic performance degradation as payload size increases
35.1x
Insert Scaling Factor
193.4x
BatchInsert Scaling
201,481x
Select Scaling
2.02 sec
Worst Case (BatchInsert XL)
0.84 μs
Best Case (Select XS)
Performance Scaling by Payload Size (Logarithmic Scale)
XS S M L XL Payload Size 0.001ms 0.01ms 0.1ms 1ms 10ms 100ms 1000ms Time (Log Scale) Operations Select Update Insert BatchInsert TransactionInsert Delete
⚠️ Critical Finding: BatchInsert performance degrades catastrophically for large payloads (193x slower)
Detailed Performance Matrix (milliseconds)
Operation ExtraSmall
(~100B)		 Small
(~1KB)		 Medium
(~10KB)		 Large
(~100KB)		 ExtraLarge
(~1MB)		 Scaling Factor Memory Impact
Insert 11.51 21.45 23.44 65.58 403.60 35.1x High
BatchInsert 11.86 10.45 21.25 243.69 2,021.07 193.4x Very High
Select 0.0008 0.001 1.69 19.06 169.24 201,481x Very High
Update 0.0007 0.0003 12.05 54.05 387.59 1.4M x Very High
Delete 0.000 0.00008 1.68 1.33 1.35 Stable Low
TransactionInsert 10.45 18.78 13.14 120.11 1,146.24 109.7x High
🎯 Key Performance Insights
Best for Small Data: Delete (stable)
Worst Scaling: Update (1.4M x)
Batch Efficiency Limit: ~10KB payload
Critical Threshold: 100KB+ payloads
Memory Explosion: 1.25GB for XL batch
💡 Optimization Recommendations

• Payload Size Limits: Keep individual records under 10KB for optimal performance

• Avoid Large Batches: BatchInsert becomes inefficient beyond 100KB total payload

• Use Streaming: For large data, implement streaming inserts instead of batching

• Delete is Optimal: Delete operations scale best across all payload sizes

• Select Degradation: Large record selection shows extreme performance impact

• Memory Monitoring: Monitor memory usage carefully with payloads > 100KB

Test Environment: .NET Framework 4.6.2 • x64 Platform • System.Data.SQLite