sql-serverrustodbcunixodbc

Efficient Connection Pooling in ODBC-MSSQL (odbc-api)?


I want to connect to MSSQL using ODBC (odbc-api) at the beginning of my server application created with hyper.rs.

I want to have a connection pool and obtain a connection within each request, which may be executed in different threads.

My question is to understand what needs to be done at the global level and what needs to be sent to each thread in order to reuse connections and avoid opening and closing different connections that could make connection management less efficient.

Please note that the application may receive a high number of requests per second.

I have simplified it in this code, without considering the presence of hyper.rs and other elements of the final implementation, but I believe it serves to give an idea:

https://gist.github.com/dertin/d5e6b031cb86fbe71c6d2272c764dedd

I appreciate if you can give me a code review. Thanks, any comments are welcome.


Solution

  • Quoting from the odbc-api guide about connection pooling. See: https://docs.rs/odbc-api/3.0.1/odbc_api/guide/index.html

    use lazy_static::lazy_static;
    use odbc_api::{Environment, sys::{AttrConnectionPooling, AttrCpMatch}};
    
    lazy_static! {
        pub static ref ENV: Environment = {
            // Enable connection pooling. Let driver decide whether the attributes of two connection
            // are similar enough to change the attributes of a pooled one, to fit the requested
            // connection, or if it is cheaper to create a new Connection from scratch.
            // See <https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/driver-aware-connection-pooling>
            //
            // Safety: This call changes global mutable space in the underlying ODBC driver manager.
            unsafe {
                Environment::set_connection_pooling(AttrConnectionPooling::DriverAware).unwrap();
            }
            let mut env = Environment::new().unwrap();
            // Strict is the default, and is set here to be explicit about it.
            env.set_connection_pooling_matching(AttrCpMatch::Strict).unwrap();
            env
        };
    }