To reuse connections, and pay the slowness only on the first connection, set
Config.DB.SetMaxIdleConns(2)
Or something like that.
As *sql.DB is a pool, this can keep two connections alive - one for your
main query, and one for the per-row extra queries.
Bill a következőt írta (2020. október 21., szerda, 12:41:54 UTC+2):
> Hello,
> I created the sql to fetch all info in one call instead of multiple calls
> per each row.
>
> One thing I noticed about golang is that when I ping my Remote DB from
> localhost (running in vscode) I get ping -> 1.573826274s. So I think this
> is the reason that all my calls to db are delayed.
> Have you any idea what can be the issue? Maybe a clean install of Golang
> in MacOSX could fix this issue? If I test the code in server (upload the
> binary) the response time is normal (200ms - 300ms).
>
> My Code to ping:
>
> begin := time.Now()
> err := Config.DB.Ping()
> log.Printf("Ping in %s (%v)", time.Since(begin), err)
> Στις Τρίτη, 20 Οκτωβρίου 2020 στις 8:19:16 μ.μ. UTC+3, ο χρήστης
> [email protected] έγραψε:
>
>> Go's database layer is generally pretty quick, I use it a lot, but your
>> code immediately sets off my DB alarms, because you are doing queries
>> within the body of another query loop, which means you're opening up lots
>> of connections, which could be slow.
>>
>> I'd reorganize as followd.
>>
>> - Cache the results of your first query into an array, it's only 6
>> results.
>> - Create two prepared statements, one for each query inside the loop.
>> - Loop over your array of 6 results, and execute each prepared statement
>> instead of parsing the query each time.
>>
>> By doing it this way, you should use 1 connection for the entire loop,
>> and you'll only be parsing statements once each.
>>
>> If this is still slow, I'd start looking at your database performance,
>> maybe.
>>
>> also, "defer rows.Close()" after you've checked if the query didn't
>> result an error. There are no rows to close if err != nil
>>
>> -- Marcin
>>
>>
>>
>> On Tue, Oct 20, 2020 at 9:52 AM <[email protected]> wrote:
>>
>>> I use Go with github.com/go-sql-driver/mysql to connect to MySQL. The
>>> performance in Go vs other languages is terrible or I'm missing something.
>>>
>>> I use Go for my REST API, an example is below:
>>>
>>> We have a table with posts. We fetch the posts, on each post we search
>>> favorites table (if user has favorited this post and if he likes it).
>>>
>>> posts := make([]*MembersModel.Post, 0, 6)
>>>
>>> postsResults, err := Config.DB.Query("SELECT id, b64, title,
>>> description, total_likes, total_favorites, published_date FROM posts
>>> ORDER BY id DESC LIMIT 6")
>>>
>>> defer postsResults.Close()
>>>
>>> if err != nil {
>>> fmt.Println(err)
>>> panic(err.Error())
>>> }
>>>
>>> for postsResults.Next() {
>>> var postID int
>>> var b64 string
>>> var title string
>>> var description string
>>> var total_likes int
>>> var total_favorites int
>>> var published_date string
>>>
>>> postsResults.Scan(&id, &b64, &title, &description, &total_likes,
>>> &total_favorites, &published_date)
>>>
>>> var count int
>>> var favorited string
>>>
>>> fetchBookmarks := Config.DB.QueryRow("SELECT COUNT(*) FROM
>>> favorites where userID = ? and postID = ? and status = ?", userID, postID,
>>> "added").Scan(&count)
>>>
>>> if fetchBookmarks != nil {
>>> fmt.Println("error")
>>> }
>>>
>>> if count == 0 {
>>> favorited = "no"
>>> } else {
>>> favorited = "yes"
>>> }
>>>
>>> var countSubmitted int
>>> var likedPost string
>>>
>>> fetchLikes := Config.DB.QueryRow("SELECT COUNT(*) FROM likes
>>> where userID = ? and postID = ? and status=?", userID, postID,
>>> "liked").Scan(&countSubmitted)
>>>
>>> if fetchLikes != nil {
>>> fmt.Println("error")
>>> }
>>>
>>> if countSubmitted == 0 {
>>> likedPost = "no"
>>> } else {
>>> likedPost = "yes"
>>> }
>>>
>>> post := &MembersModel.JobList{
>>> PostID: b64,
>>> Title: title,
>>> Description: description,
>>> Likes: total_likes,
>>> PubDate: published_date,
>>> Bookmarked: favorited,
>>> Liked: likedPost,
>>> }
>>>
>>> posts = append(posts, post)
>>> }
>>>
>>> Average time to fetch these results -> 10 seconds!
>>>
>>> If I exclude the MYSQL calls inside the loop, the time to fetch these
>>> results is 300ms.
>>>
>>> --
>>> You received this message because you are subscribed to the Google
>>> Groups "golang-nuts" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to [email protected].
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/golang-nuts/e3f758d8-595f-4f0d-9aa6-d0b87899007fo%40googlegroups.com
>>> .
>>>
>>
--
You received this message because you are subscribed to the Google Groups
"golang-nuts" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/golang-nuts/4959540b-199b-4a9d-88a5-c95bbe3117bfn%40googlegroups.com.
