1

I am trying to load a huge data set from DB. 

    func main() {
        db, err := sql.Open("mysql", "root:pass1@tcp(127.0.0.1:3306)/tuts")

        if err != nil {
            log.Print(err.Error())
        }
        defer db.Close()
        results, err := db.Query("SELECT id, name FROM tags")
        if err != nil {
            panic(err.Error()) 
        }

        for results.Next() {
            var tag Tag
            err = results.Scan(&tag.ID, &tag.Name)
            if err != nil {
                panic(err.Error()) 
            }

            log.Printf(tag.Name)
        }
}

Does the program load all the records to memory in single shot? Or is there any way to specify the fetch size so that program will load only n of rows at a time? Assuming there are million rows in the database, i would like to fetch 1000 records each time.

Suren Raju
  • 3,012
  • 6
  • 26
  • 48
  • 1
    What about `select count(*) from tags`? And then `SELECT id, name FROM tags LIMIT 100`. https://www.guru99.com/sqlite-query.html#4 – RickyA Apr 16 '19 at 09:01
  • 1
    No, it does not load everything into memory. It fetches data as required as Next() is called. – Peter Apr 16 '19 at 09:07
  • @RickyA yes, we can write a custom logic. But i am wondering if there is an option from standard library. – Suren Raju Apr 16 '19 at 09:25
  • @Peter does it make million calls to db of million rows matching rows? – Suren Raju Apr 16 '19 at 09:26
  • @SurenRaju you can take tcpdump/wireshark and see what actual happens under the hood. – zerkms Apr 16 '19 at 09:31
  • 1
    You should add `defer results.Close()`, too, and you can declare `var tag Tag` outside of the loop, and reuse the same variable inside – Elias Van Ootegem Apr 16 '19 at 11:12

1 Answers1

1

This will work just fine for a single row and millions of rows. Most SQL implementations have the notion of batches when reading. They load data from the disk as needed and keeps RAM usage low/constant as needed.

For example if you are selecting 1000 rows. The database may load the first 100 rows to the RAM. While you are calling Next(), for example when you reach the 50th row, the database grabs another hundred (rows 100 to 201 for example).

Seaskyways
  • 3,630
  • 3
  • 26
  • 43
  • I may be wrong but I think OP is asking if the program (client) loads it all into memory, not if the database loads it all into memory. – Adrian Apr 16 '19 at 14:04
  • Yes, the program is using the database's functionality. What I said still holds. – Seaskyways Apr 16 '19 at 14:46
  • Not really. Your answer talks about what the database will do with its memory, the question is about what the client will do with its memory. These are likely running on different machines, so what the database does with the memory on the database host is largely irrelevant to the matter of the client using memory on its own host. – Adrian Apr 16 '19 at 14:53
  • I am facing a similar problem. Can come one clairfy if the sql library batches the results or not ? – leoOrion Mar 27 '22 at 07:53