Improving INSERT-per-second performance in SQLite requires optimizing various factors such as transaction management, write settings, and indexing. Here are key strategies to significantly boost SQLite insert performance:

1. Use Transactions Effectively

SQLite commits changes to disk after every INSERT by default, which is slow. Instead, group multiple inserts into a single transaction.

Single Transaction for Multiple Inserts:

1BEGIN TRANSACTION;
2
3INSERT INTO my_table (column1, column2) VALUES (value1, value2);
4INSERT INTO my_table (column1, column2) VALUES (value3, value4);
5-- Repeat for multiple inserts...
6
7COMMIT;

• Why?: Committing after every insert incurs disk I/O overhead. Grouping inserts reduces the number of commits.

2. Use PRAGMA synchronous to Control Write Behavior

The synchronous setting controls SQLite's syncing with the disk. Lowering it improves performance but at the cost of some durability.

Set Synchronous to OFF or NORMAL:

PRAGMA synchronous = OFF;   -- Fastest, but risky on crashes.
PRAGMA synchronous = NORMAL; -- A balance between performance and durability.

• OFF: SQLite doesn’t wait for writes to be confirmed by the disk.
• NORMAL: SQLite syncs less aggressively, improving performance while still offering reasonable safety.

3. Use PRAGMA journal_mode to Optimize Journaling

SQLite uses a journal to ensure database integrity. The journal mode impacts performance during writes.

Set Journal Mode to MEMORY or WAL:

PRAGMA journal_mode = WAL;  -- Write-Ahead Logging (Recommended)
PRAGMA journal_mode = MEMORY; -- Store journal in memory (faster, but risky).

• WAL (Write-Ahead Logging): Allows concurrent reads and improves write performance.
• MEMORY: Speeds up writes by keeping the journal in memory but loses durability after crashes.

WAL mode is generally recommended for better INSERT performance and concurrency.

4. Use PRAGMA cache_size to Increase Cache

Increasing the cache size reduces disk I/O by caching pages in memory.

Set Cache Size:

PRAGMA cache_size = 10000; -- Cache 10,000 pages (~40 MB, assuming 4 KB page size).

• Adjust the cache size based on your available system memory.

5. Use Prepared Statements

Prepared statements reduce query parsing overhead when inserting multiple records.

Example Using Python:

1import sqlite3
2
3conn = sqlite3.connect('example.db')
4cursor = conn.cursor()
5
6cursor.execute("BEGIN TRANSACTION;")
7
8# Use a prepared statement for faster insertion
9insert_query = "INSERT INTO my_table (column1, column2) VALUES (?, ?)"
10data = [(1, 'a'), (2, 'b'), (3, 'c')]
11
12cursor.executemany(insert_query, data)
13
14conn.commit()
15conn.close()

• Why?: Prepared statements compile the query once and reuse it, saving time.

6. Disable Indexes During Bulk Inserts

Indexes slow down inserts because SQLite has to update indexes for every row. Disable indexes temporarily during bulk inserts and recreate them afterward.

Steps:

1. Drop the index.
2. Perform bulk inserts.
3. Recreate the index.

Example:

1DROP INDEX IF EXISTS my_index;
2
3BEGIN TRANSACTION;
4INSERT INTO my_table (column1, column2) VALUES (value1, value2);
5COMMIT;
6
7CREATE INDEX my_index ON my_table (column1);

7. Avoid Unnecessary Constraints

• Avoid using FOREIGN KEY constraints if they are not strictly necessary.
• If constraints are needed, group inserts into transactions to minimize constraint checks.

8. Use Bulk Insert with INSERT ... VALUES

Instead of individual INSERT statements, use bulk inserts:

Example:

INSERT INTO my_table (column1, column2)
VALUES (value1, value2), (value3, value4), (value5, value6);

This reduces query overhead.

9. Optimize File System and Disk Access

• Use an SSD instead of an HDD for faster disk writes.
• Use tmpfs (on Linux) or RAM disks to store the SQLite database in memory for temporary operations.

10. Measure Performance

Use SQLite's built-in timing feature to analyze query performance:

.timer ON

Summary Table of Optimizations

By applying these optimizations, you can significantly improve INSERT-per-second performance in SQLite, especially during bulk data operations. 🚀