Why is database/sql in Go Slower Than Direct Database Querying, and How Can I Fix It?

Investigating Query Performance Disparity: database/sql vs. Direct Querying

Queries executed using the Go database/sql package are significantly slower than equivalent queries executed directly against the database. To address this disparity, it is essential to delve into the underlying mechanisms.

Connection Management in database/sql

The sql.DB object represents a pool of connections, not a single connection. When sql.Open is called, it initializes the pool but may not establish any connections. Only when a query is requested does a new connection get created.

Impact on Query Timing

In the provided code snippet, the first query encounters a performance hit because it triggers the creation of a new database connection. The second query also exhibits a performance gap due to the lack of connection reuse. Each query establishes a new connection instead of utilizing an existing idle connection from the pool.

Releasing Connections Back to the Pool

To resolve this issue, it is crucial to release connections back to the pool after each query. This can be achieved by retaining the first return value of db.Query, which represents the query results, and invoking .Close() on it.

Establishing an Idle Connection

To start with an available connection in the pool, call Ping on the sql.DB object after initialization. This will force the creation of an initial connection.

Prepared Statements and Positional Parameters

Prepared statements are utilized when queries contain arguments. The Postgres protocol enables parameters instead of direct insertion of values into the query string. This separate parameterization allows for more efficient handling of queries.

Code Example

The following code snippet illustrates how to correctly manage connections and reuse prepared statements:

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/lib/pq"
    "time"
)

func main() {
    // Initialize database connection with initial idle connection
    db, err := sql.Open("postgres", "postgres:///?sslmode=disable")
    if err != nil {
        panic(err)
    }
    if err := db.Ping(); err != nil {
        panic(err)
    }

    for i := 0; i < 5; i++ {
        // Prepare query
        query := "select 1 where true"
        stmt, err := db.Prepare(query)
        if err != nil {
            panic(err)
        }

        // Execute and time query
        firstQueryStart := time.Now()
        rows, err := stmt.Query()
        firstQueryEnd := time.Now()
        if err != nil {
            panic(err)
        }

        // Release connection back to pool
        rows.Close()

        fmt.Println(fmt.Sprintf("query #%d took %s", i, firstQueryEnd.Sub(firstQueryStart).String()))
    }
}

By implementing these optimizations, the query performance gap can be significantly reduced, providing comparable execution times to direct querying.

The above is the detailed content of Why is database/sql in Go Slower Than Direct Database Querying, and How Can I Fix It?. For more information, please follow other related articles on the PHP Chinese website!