如何在Go中快速获取多行？

Question

I have troubles with performance when I get 20k-50k and more rows from SELECT * FROM table in rows.scan(&a, &b...). I don't know how to use concurrency in this case because I need to use rows.Next() for iterating, and I can't do it in concurrency.

Performance drops only when I scan results from rows to structure's fields in rows.Scan.

The query takes 5-15 ms, but scanning takes (40k rows) 800-2000ms.

Thank you!

Answer 1

Since 'rows.Next()' is sequential, you would need to split your query into multiple statements, and then work concurrently on them.

func main() {

    queries := []string{
        "SELECT * FROM abc where a < 10000",
        "SELECT * FROM abc where a >= 10000 && a <= 30000",
        "SELECT * FROM abc where a > 30000",
    }

    for _, query := range queries {
        go dbCall(query)
    }
}

func dbCall(query string) {

    rows, _ := db.Query(query)
    for rows.Next() {
        var a, b, c int
        _ = rows.Scan(&a, &b, &c)
        // Process row
    }
}

Use channels, locks, etc when necessary, based on your use case.