No way to reuse prepared statements

[xokelis00-glitch]

Hi Ivan Ukhov,

First of all thank you for writing this, it saved me so much time for my own use case which is a personal project I'm working on.

That said, I'm an Electrical Engineer, not a Software Engineer, and so I am not well versed in working with git, so I'm not sure how to do pulls, pushes, contributions, etc, so I have decided to contribute in a way that I know how.

I noticed that this smol library, which I like a lot, did not have an easy way to reuse prepared statements which can net a 15% performance improvement when inserting a large number of rows into a table. I had worked on a zig version of your library for personal use and had gotten the prepared statement re-usability working there so I decided to download your code and add the necessary ffi function to get it working here.

It is inside in the 'statements.rs' file under 'impl Statement' code, and it looks like this:

/// Contrary to the intuition of many, reset() does not reset the bindings on a prepared statement.
/// Use this routine to reset all host parameters to NULL.
#[inline]
pub fn clear_bindings(&mut self) -> Result<()> {
    let result = unsafe { ffi::sqlite3_clear_bindings(self.raw.0) };
    if result != ffi::SQLITE_OK {
        return Err(crate::error::Error {
            code: Some(result as isize),
            message: None,
        })
    }
    Ok(())
}

I also added a convenience function to that same 'impl' block to allow me to just read the result of a row count:

/// Display select statement text.
#[inline]
pub fn column_text(&mut self) -> String {
    let ptr = unsafe { ffi::sqlite3_column_text(self.raw.0, 0) };
    let str = unsafe { c_str_to_string!(ptr) };
    str
}

Below are my results for the net performance improvement from this change. Note that I'm using random data each time I run the test, and that I'm using 'conn.execute("BEGIN;")' and 'conn.execute("COMMIT;")' to start and end transactions, which feels a bit hacky but it works.

Results below:

Testing: prepared statements without transaction...
Table 'users' row count: 1000
Elapsed 8.60s for 1000 inserts, 116.26 inserts per second

Testing: prepared statements with transaction...
Table 'users' row count: 1000
Elapsed 11.16ms for 1000 inserts, 89597.71 inserts per second

Testing: reusing prepared statements with transaction...
Table 'users' row count: 1000
Elapsed 9.63ms for 1000 inserts, 103799.05 inserts per second

sqlite3 version: 3049001

Code used to get above results:

pub fn test() -> Result<()> {
    if !Path::new("db").exists() {
        _ = fs::create_dir("db")?;
    }

    let db_path = Path::new("db/test");
    let conn = sqlite::open(db_path)?;

    let drop_table = "DROP TABLE users;";
    let create_table = "
        CREATE TABLE IF NOT EXISTS users (
            acct_num INTEGER PRIMARY KEY,
            name TEXT, 
            age INTEGER,
            phone_num TEXT,
            net_worth REAL
        );
    ";

    // Testing: prepared statements without transaction
    println!("Testing: prepared statements without transaction...");
    conn.execute(create_table)?;

    let now = Instant::now();
    for _ in 0..1_000 {
        let insert = "INSERT INTO users VALUES(?, ?, ?, ?, ?);";
        let mut stmt = conn.prepare(insert)?;

        let acct_num = rand_int(1234567890, 9999999999) as i64;
        let name = rand_str(rand_int(7, 25) as usize);
        let age = rand_int(18, 100) as i64;
        let phone_num = rand_str(10);
        let net_worth = rand_real(1000.0, 9_999_999.99);

        stmt.bind((1, acct_num))?;
        stmt.bind((2, name.as_str()))?;
        stmt.bind((3, age))?;
        stmt.bind((4, phone_num.as_str()))?;
        stmt.bind((5, net_worth))?;
    
        stmt.next()?;               // sqlite3_step() execute statement
    }
    let elapsed = now.elapsed();

    let get_row_count = "SELECT COUNT(?) FROM users;";
    let mut stmt = conn.prepare(get_row_count)?;
    stmt.bind((1, "age"))?;

    while let Ok(State::Row) = stmt.next() {
        println!("Table 'users' row count: {}", stmt.column_text()); // <- I added this function
    }

    let i_per_s = 1_000_000.0 / elapsed.as_micros() as f64 * 1000.0;
    println!("Elapsed {:.2?} for 1000 inserts, {:.2} inserts per second\n", elapsed, i_per_s);
    conn.execute(drop_table)?;

    // Testing: prepared statements with transaction
    println!("Testing: prepared statements with transaction...");
    conn.execute(create_table)?;

    let now = Instant::now();
    conn.execute("BEGIN;")?;        // BEGIN manually begins a transaction
    for _ in 0..1_000 {
        let insert = "INSERT INTO users VALUES(?, ?, ?, ?, ?);";
        let mut stmt = conn.prepare(insert)?;

        let acct_num = rand_int(1234567890, 9999999999) as i64;
        let name = rand_str(rand_int(7, 25) as usize);
        let age = rand_int(18, 100) as i64;
        let phone_num = rand_str(10);
        let net_worth = rand_real(1000.0, 9_999_999.99);

        stmt.bind((1, acct_num))?;
        stmt.bind((2, name.as_str()))?;
        stmt.bind((3, age))?;
        stmt.bind((4, phone_num.as_str()))?;
        stmt.bind((5, net_worth))?;
    
        stmt.next()?;               // sqlite3_step() execute statement
    }
    conn.execute("COMMIT;")?;       // END TRANSACTION is an alias for COMMIT
    let elapsed = now.elapsed();

    let get_row_count = "SELECT COUNT(?) FROM users;";
    let mut stmt = conn.prepare(get_row_count)?;
    stmt.bind((1, "age"))?;

    while let Ok(State::Row) = stmt.next() {
        println!("Table 'users' row count: {}", stmt.column_text()); // <- I added this function
    }

    let i_per_s = 1_000_000.0 / elapsed.as_micros() as f64 * 1000.0;
    println!("Elapsed {:.2?} for 1000 inserts, {:.2} inserts per second\n", elapsed, i_per_s);
    conn.execute(drop_table)?;

    // Testing: prepared statements with transaction
    println!("Testing: reusing prepared statements with transaction...");
    conn.execute(create_table)?;

    let now = Instant::now();
    conn.execute("BEGIN;")?;        // BEGIN manually begins a transaction
    let insert = "INSERT INTO users VALUES(?, ?, ?, ?, ?);";
    let mut stmt = conn.prepare(insert)?;
    for _ in 0..1_000 {
        let acct_num = rand_int(1234567890, 9999999999) as i64;
        let name = rand_str(rand_int(7, 25) as usize);
        let age = rand_int(18, 100) as i64;
        let phone_num = rand_str(10);
        let net_worth = rand_real(1000.0, 9_999_999.99);

        stmt.bind((1, acct_num))?;
        stmt.bind((2, name.as_str()))?;
        stmt.bind((3, age))?;
        stmt.bind((4, phone_num.as_str()))?;
        stmt.bind((5, net_worth))?;
    
        stmt.next()?;               // sqlite3_step() execute statement

        // I added this function to fascilitate statement reusability
        stmt.clear_bindings()?;     // sqlite3_clear_bindings() for new bindings
        //////////////////////////

        stmt.reset()?;              // sqlite3_reset() to beginning so statement can be executed again
    }
    conn.execute("COMMIT;")?;       // END TRANSACTION is an alias for COMMIT
    let elapsed = now.elapsed();
    
    let get_row_count = "SELECT COUNT(?) FROM users;";
    let mut stmt = conn.prepare(get_row_count)?;
    stmt.bind((1, "age"))?;

    while let Ok(State::Row) = stmt.next() {
        println!("Table 'users' row count: {}", stmt.column_text()); // <- I added this function
    }

    let i_per_s = 1_000_000.0 / elapsed.as_micros() as f64 * 1000.0;
    println!("Elapsed {:.2?} for 1000 inserts, {:.2} inserts per second\n", elapsed, i_per_s);
    conn.execute(drop_table)?;

    remove_file(db_path)?;

    println!("sqlite3 version: {}", sqlite::version());
    Ok(())
}

If you're so incline feel free to add these changes in the next revision of your library.
Thanks again for writing this.

xokelis00

[IvanUkhov]

Thank you for the detailed issue! I was not aware that the reset is not clearing bindings, but now reading the documentation, I see it does not indeed. I used this crate for a project where I was doing a lot of inserts, and I wanted it to be fast. Hence I was reusing the same statement but binding different values, just like what you describe. But that makes me think that whenever one wants to reuse, one will also want to rebind. From this perspective, I do not see much utility in clearing bindings. Could you please elaborate more on why one would want to clear bindings?

[xokelis00-glitch]

Honestly I thought they needed to be cleared because that's how I've seen re-usability done in C, so I thought not resetting bindings to null would cause performance issues or errors, but I tried the same code without resetting the bindings and it worked just fine.

I decided to look into it a bit further and it turns out that the only time this would matter is if you're inserting in a table with a large number of NULL Values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) and you don't want to have to manually bind NULL to each value individually. Instead, you reset all to null, and then just add bindings to the ones you need.

In fact now that I think about it taking the extra step to reset all bindings to null, just to turn around and override them with fresh values seems unnecessary, other than in the special case that I described above.

[IvanUkhov]

Yes, if there is some code reusing a prepared statement in a loop, it probably does not try to count first how many NULLs there are in the current iteration and switch between two branches depending on the count; it simply goes over the parameters and binds each one anew (and one can bind a NULL by using ()).