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?
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.