Using QueryRow through statement or database within Go

Question

The query execution methods below issue exactly the same (amount of 3) queries in DB which is fine and understandable - see below. However, what I want to know is, as far as Go goes is there a real difference between two as in memory, cpu, cache, connection usages so on?

Queries

2020-02-22T12:29:23.858393Z 41 Prepare SELECT id, uuid, name FROM users WHERE id = ?
2020-02-22T12:29:23.859601Z 41 Execute SELECT id, uuid, name FROM users WHERE id = 1
2020-02-22T12:29:23.861607Z 41 Close   stmt

Methods

func Select1(query string, args ...interface{}) (*sql.Row, error) {
    stmt, err := DB.Prepare(query)
    if err != nil {
        return nil, err
    }
    defer stmt.Close()

    return stmt.QueryRow(args...), nil
}

func Select2(query string, args ...interface{}) *sql.Row {
    return DB.QueryRow(query, args...)
}

Because your sql has a parameter and you're passing arguments in to the `DB.QueryRow` call in `Select2`, it will, underneath, create a prepared statement just like the one you create manually in `Select1`, therefore, in my estimation, the two are equivalent in terms of cost. If you are executing some sql that takes no parameters, then `DB.QueryRow` has no reason, I believe, to create a prepared statement and therefore I would consider `Select1` to be more wasteful. — mkopriva, Feb 22 '20 at 15:53
The main advantage of a prepared statement comes from its reusability but since that is not being utilized here, `Select1`, in my mind at least, is a waste of the programmer's time, and potentially the computer's as well. Caveat: the above is based on my very crude understanding of what's going on under the hood of the driver I use, other drivers may do their work differently... so, if this is really important to you then you should profile your code, Go does provide tools for that, you know? — mkopriva, Feb 22 '20 at 15:53

score 1 · Answer 1 · answered Feb 23 '20 at 14:19

1

Adding to what @mkopriva said in the comments about the client-side, there are server-side implications for using prepared statements.

It is important to note that using *sql.DB employs connection pooling:

DB is a database handle representing a pool of zero or more underlying connections

Why is this important? Well concurrent access may trigger multiple physical connections. And when using prepared statements - which are are meant to be reused - are only effective when used on the same database session (i.e. connection).

Connection pooling (using *sql.DB) by its nature does not guarantee this. Thus with 100 concurrent queries - there could in theory be 100 concurrent database connections generating - not one - but up to a 100 prepared statements on the server-side.

You could obtain a single connection to the DB via sql.DB.Conn() where:

Queries run on the same Conn will be run in the same database session.

meaning any previously generated prepared statement will get the benefits of reuse (as they are using the same "session").

In conclusion, weigh the benefits of a prepared statement on both client and server impact, and if you are actually gaining those benefits by your concurrent use.

answered Feb 23 '20 at 14:19

colm.anseo

19,337
4
43
52

Thanks for the answer. Do you mean we should explicitly call `sql.DB.Conn` before issuing any queries or am I getting it wrong? As far as I can see from my debugging, prepared statements useful only when dealing with user data just to avoid injection. – BentCoder Feb 23 '20 at 14:57
Without know the full lifecycle of your DB use, I'd be only guessing. But in my personal experience, connection pooling is very liberating and greatly simplifies client code. Unless there's a compelling need to maintain a DB session, I would avoid the headache of managing your own individual connections. – colm.anseo Feb 23 '20 at 15:05
Here's one [use case](https://stackoverflow.com/a/56507430/1218512) where I wanted to avoid maintaining a single session. So in general I'd try to avoid sessions, to keep things simple. – colm.anseo Feb 23 '20 at 15:13
I guess using go-sql-driver handles everything for us behind the scene. I haven't seen any note regarding to we should handle things manually anywhere. Examples are all same on the web and doc itself. I haven't seen a single ocasion one uses `sql.DB.Conn` so far. – BentCoder Feb 23 '20 at 15:14

score 1 · Accepted Answer · answered Feb 27 '20 at 19:02

I've spent more time on testing and profiling so this is my finding - also confirms @mkopriva's comments.

If a SELECT statement is used, whether with or without query placeholders, you should prefer using DB.Query and/or DB.QueryRow rather than DB.Prepare & STMT.Exec combination. When I checked the MySQL query logs, it looked like DB.Query and DB.QueryRow acted as if they were DB.Exec for queries where there was no argument placeholders. This means only one (Query) network round-trip instead of three (Prepare, Execute and Close)!

So why not just use DB.Exec then? Because it is used for queries that don't return result set. By the way, DB.Exec releases its connection straight back to the pool but DB.Query keeps its connection out of the pool until rows.Close() is called. If you forget calling it, this might lead to connection "leak" and unavailability of connections. So technically, DB.Query comes with its own risks if you are not doing things properly.

Last note, if your INSERT, UPDATE and DELETE query arguments are SQL injection free then use DB.Exec otherwise stick with DB.Prepare instead. For manually prepare query arguments, you can use fmt.Sprintf() function.

`DB.Exec` supports argument placeholder. https://pkg.go.dev/database/sql#DB.Exec — harrrrrrry, Aug 06 '21 at 14:23

Using QueryRow through statement or database within Go

2 Answers2