Why does rows.Next() depends on DB response rate?

Question

I'm using sqlx, pgx and postgresql. There are two DB`s. First - based on VPS server (slow one), second - installed locally on my PC (fast one). I have a question about this code:

var ordersSlice []OrdersModel    
start := time.Now()
query = `select * from get_all_orders();`
rows, err = db.Queryx(query)
log.Printf("Query time %s", time.Since(start)) // avg in slow DB - 62ms, avg in fast DB - 20ms

if rows == nil || err != nil {
    fmt.Println(err)
    fmt.Println("no result")
    response.WriteHeader(http.StatusInternalServerError)
    return
}

// db.Close() to check if rows.Next() depends on DB
start = time.Now()
for rows.Next() {
    var order OrdersModel

    err = rows.StructScan(&order)
    if err != nil {
        fmt.Println(err)
    }
    ordersSlice = append(ordersSlice, order)
}
log.Printf("Sturct scan time %s", time.Since(start)) // avg in slow DB - 14.4S, avg in fast DB - 9ms

I mean rows.Next() takes more time, than db.Queryx(query) with the slow DB. It takes 14.4 sec to handle result. Why so? The first part of code with db.Queryx(query), should depends on the db response rate. As I see, it is db.Queryx(query) should depend on the db response rate, since the query is executed here and the results are placed in rows. And in rows.Next(), the results are simply being processed. When I assumed rows.Next() somehow depends on DB, I closed connection before row.Next() loop execution, in this way db.Close(). But there was no error. Records was handled.

Cycle for rows.Next() don't communicate with DB, so why does it depends on the DB response rate?

Your assumption that calling `db.Close()` should cause error is not true - quote from the doc (https://golang.org/pkg/database/sql/#DB.Close): `Close closes the database and prevents new queries from starting. Close then waits for all queries that have started processing on the server to finish.` — ain, Mar 29 '20 at 10:56
"Cycle for rows.Next() don't communicate with DB" That's incorrect too. Each call to Next fetches the next row from the database. In other words, Query doesn't slurp all results at once. — Peter, Mar 29 '20 at 11:09
"Cycle for rows.Next() don't communicate" -- Or course it does. Where do you think it gets the results if not from the database? — Jonathan Hall, Mar 29 '20 at 11:19
@Flimzy I thought data from db filled in there - db.Queryx(query) and located in the rows. And rows.Next() just iterates data and allows to work with it. — Metamorphosis, Mar 29 '20 at 17:47
Yes, rows.Next iterates the data. But that data comes from the database. I'm not sure what you're trying to say, why you think it wouldn't read that from the database. — Jonathan Hall, Mar 29 '20 at 19:01

Grigoriy Mikhalkin · Accepted Answer · 2020-03-29T11:54:02.607

Short answer is: YES, Rows.Next() communicates with DB.

From database/sql docs:

Rows is the result of a query. Its cursor starts before the first row of the result set

But, actually, implementation details are left to DB driver.

For example, in lib/pq, Query executes either simple query or extended query protocol (see postgres docs for more details) and after it receives RowDescription object, it parses it into internal structure(rowHeader). Then,rows.Next() uses it to fetch actual data.

You can see, that pgx does something similar. Query method executes one of protocols and saves data from RowDescription in ResultReader structure. Then rows.Next uses it to fetch data from DB.

Why does rows.Next() depends on DB response rate?

1 Answers1