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 <billyp...@gmail.com> 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 golang-nuts+unsubscr...@googlegroups.com.
> 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 golang-nuts+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/golang-nuts/CA%2Bv29Lttg5yNABS78o%2BRwKvhhkQ5n-pULJD9_2P2gkruvz1x7Q%40mail.gmail.com.
