https://iosdevelopers.slack.com/archives/C031X84F6/p1747356646966749?thread_ts=1747345752.479769&cid=C031X84F6

SqliteGranularityExperiment.swift

class SqliteGranularityExperiment {
    func demonstration() {
        // Define the C-compatible callback function using @convention(c)
        let traceCallback: @convention(c) (
            UInt32,               // traceFlag
            UnsafeMutableRawPointer?,  // context
            UnsafeMutableRawPointer?,  // statement
            UnsafeMutableRawPointer?   // nanoseconds (for PROFILE)
        ) -> Int32 = { traceFlag, _, statementPointer, nanosecondsPointer in
            guard
                traceFlag == SQLITE_TRACE_PROFILE,
                let statementPointer,
                let nanosecondsPointer
            else {
                return 0
            }

            let stmt = OpaquePointer(statementPointer)
            let durationNs = nanosecondsPointer.assumingMemoryBound(to: UInt64.self).pointee

            let formatter = NumberFormatter()
            formatter.numberStyle = .decimal

            if let cSql = sqlite3_expanded_sql(stmt) {
                let sql = String(cString: cSql)
                print("PROFILE:", sql)
                print("  Duration:", formatter.string(from: durationNs as NSNumber)!, "ns")
                sqlite3_free(cSql)
            }

            return 0
        }

        let url = URL.temporaryDirectory.appendingPathComponent("test.db")
        try? FileManager.default.removeItem(at: url)

        var database: OpaquePointer?

        defer { sqlite3_close(database) }

        guard sqlite3_open_v2(url.path, &database, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, nil) == SQLITE_OK else {
            printError("unable to open file", in: database)
            sqlite3_close(database)
            return
        }

        guard sqlite3_exec(database, "CREATE TABLE test(id INTEGER PRIMARY KEY, name TEXT);", nil, nil, nil) == SQLITE_OK else {
            printError("create failed", in: database)
            return
        }

        var statement: OpaquePointer?
        guard sqlite3_prepare_v2(database, "INSERT INTO test(name) VALUES (?);", -1, &statement, nil) == SQLITE_OK else {
            printError("prepare failed", in: database)
            return
        }

        defer { sqlite3_finalize(statement) }

        guard sqlite3_exec(database, "BEGIN TRANSACTION;", nil, nil, nil) == SQLITE_OK else {
            printError("begin transaction failed", in: database)
            return
        }

        let numberStringFormatter = NumberFormatter()
        numberStringFormatter.numberStyle = .spellOut

        for i in 0 ..< 1_000_000 {
            let string = numberStringFormatter.string(from: i as NSNumber)!
            guard sqlite3_bind_text(statement, 1, string, -1, SQLITE_TRANSIENT) == SQLITE_OK else {
                printError("Bind of \(i) failed", in: database)
                return
            }

            guard sqlite3_step(statement) == SQLITE_DONE else {
                printError("Insert \(i) step failed", in: database)
                return
            }

            guard sqlite3_reset(statement) == SQLITE_OK else {
                printError("Reset \(i) failed", in: database)
                return
            }
        }

        guard sqlite3_exec(database, "COMMIT;", nil, nil, nil) == SQLITE_OK else {
            printError("commit failed", in: database)
            return
        }

        // Now that most of the records have been inserted, register trace callback

        guard sqlite3_trace_v2(database, UInt32(SQLITE_TRACE_PROFILE), traceCallback, nil) == SQLITE_OK else {
            printError("Enabling trace failed", in: database)
            return
        }

        guard sqlite3_bind_text(statement, 1, "Alice", -1, SQLITE_TRANSIENT) == SQLITE_OK else {
            printError("Alice failed", in: database)
            return
        }

        guard sqlite3_step(statement) == SQLITE_DONE else {
            printError("Alice step failed", in: database)
            return
        }

        guard sqlite3_reset(statement) == SQLITE_OK else {
            printError("Reset failed", in: database)
            return
        }

        guard sqlite3_bind_text(statement, 1, "Bob", -1, SQLITE_TRANSIENT) == SQLITE_OK else {
            printError("Bob failed", in: database)
            return
        }

        guard sqlite3_step(statement) == SQLITE_DONE else {
            printError("Bob step failed", in: database)
            return
        }

        sqlite3_finalize(statement) // finalize the prior one

        guard sqlite3_prepare_v2(database, "SELECT COUNT(*) FROM test WHERE name LIKE '%eleven%';", -1, &statement, nil) == SQLITE_OK else {
            printError("prepare failed", in: database)
            return
        }

        guard sqlite3_step(statement) == SQLITE_ROW else {
            printError("SELECT failed", in: database)
            return
        }

        let count = sqlite3_column_int(statement, 0)
        print("count = \(count)")
    }

    func printError(_ message: String, in database: OpaquePointer?) {
        let code = sqlite3_errcode(database)
        let string = sqlite3_errmsg(database).flatMap(String.init(cString:)) ?? "Unknown error"
        print(message, "code:", code, "message:", string)
    }
}

With a macOS target, that produced:

PROFILE: INSERT INTO test(name) VALUES ('Alice');
  Duration: 1,000,000 ns
PROFILE: INSERT INTO test(name) VALUES ('Bob');
  Duration: 0 ns
count = 19900
PROFILE: SELECT COUNT(*) FROM test WHERE name LIKE '%eleven%';
  Duration: 128,000,000 ns

There is some variability re Alice and Bob (sometimes they come in under 1ms on my hardware, sometimes over), but the complicated search within an unindexed TEXT column in a table with 1m records, consistently was over 1ms. But the more notable observation is that in iOS and macOS, it always appears to be a multiple of 1,000,000 nanoseconds, i.e., 1ms.