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/c8aa0609-fccb-4cac-83d4-309375d480c5n%40googlegroups.com.
