Loading the drinks based on distance takes forever

[timoh]

The data model / how the data is queried needs a redesign & refactoring.

[techstalgist]

The time between REQUEST_DRINKS and RECEIVE_DRINKS actions was about 22 seconds. So I suppose the backend performance should be improved first?

[techstalgist]

Current relations:

class AlcoDrink
  has_many :alco_avails
  has_one :review

class AlcoAvail
  belongs_to :alco_location
  belongs_to :alco_drink

class AlcoLocation
  has_many :alco_avails

class Review
  belongs_to :alco_drink

So now the problem is that there is one query to AlcoAvails per AlcoDrink, one query to Reviews per AlcoDrink, and one query to AlcoLocation per AlcoAvail.

• if embeds_many and embeds_one relations are used, does that mean that a single AlcoLocation needs to be stored separately for each AlcoAvail? I.e. data duplication cannot be avoided when using embedding? http://stackoverflow.com/questions/12319407/mongoid-belongs-to-and-embedded-in-hot-can-id-do --> "the embedded model can't be referenced [ in other models ]".
• is it correct to say that the current home/distanced -implementation would be slow in an SQL database as well, because in an SQL database it would be doing an equal amount of SELECT queries into the database? I.e. if we were using a relational database, we would have to change the implementation to use JOINs instead of making thousands of separate DB queries in a loop?

[techstalgist]

Another comment: given the data model shown above, what is the benefit of using document-database like MongoDB? Wouldn't an SQL database be easier to use in this case? I don't know much about MongoDB, but I know that an SQL database would handle the JOINs very quickly given this amount of data (from my development DB):

[1] pry(main)> AlcoDrink.count
=> 326
[2] pry(main)> AlcoAvail.count
=> 4130
[3] pry(main)> AlcoLocation.count
=> 35

Of course, if we switch to embedding, then the data can be stored in the database in the format that is suitable for our purposes (AlcoAvails embedded in AlcoDrinks, instead of in a separate table like in SQL DB). But what I don't get is how AlcoLocation should be stored in MongoDB so that we could store one location only once in DB, not once per AlcoDrink.

[timoh]

I think this calls for a whiteboarding session.

MongoDB is slower to query than SQL in a general case, yes. MongoDB, on the other hand, is migration free and has a dynamic data model (schemaless). Mongo is thus great for prototyping.

So I would first try to optimize the data model and only if the perf still isn't enough (which I doubt), we can look into switching the whole DB.

[timoh]

It ours is similar to the N+1 problem, some solutions discussed in these posts:

• Batching solution: https://secure.phabricator.com/book/phabcontrib/article/n_plus_one/
• Eager loading solution: https://www.sitepoint.com/silver-bullet-n1-problem/

Eager loading for Mongoid:

• http://alexanderwong.me/post/16505736790/no-find-in-batches-in-mongoid-rails-mongoid
• http://blog.appsignal.com/blog/2013/06/13/reducing-mongodb-queries.html
• http://stackoverflow.com/questions/11126606/mongoid-how-to-chain-eager-loading
• https://docs.mongodb.com/ecosystem/tutorial/mongoid-queries/#eager-loading
• http://qiita.com/karloku/items/8752aaa845137a3c3034

[timoh]

Yet another solution, which could (and perhaps should?) be combined with the previously mentioned could be pagination of the API call.

Pagination with Rails and Mongoid (ORM):

• Kaminari gem for Mongoid: https://github.com/amatsuda/kaminari
• http://stackoverflow.com/questions/8549256/mongoid-pagination

[timoh]

Also about general schema design:

• https://www.percona.com/blog/2013/08/01/schema-design-in-mongodb-vs-schema-design-in-mysql/
• http://blog.mongodb.org/post/87200945828/6-rules-of-thumb-for-mongodb-schema-design-part-1
• http://blog.mongodb.org/post/72874267152/transitioning-from-relational-databases-to-mongodb
• https://docs.mongodb.com/manual/data-modeling/

[techstalgist]

Pagination would definitely improve the user experience as well. How should sorting be implemented with pagination in place? It should sort all drinks, not just the ones visible on the current page. Now sorting has been implemented on the client side.

[timoh]

Sorting a paginated Mongoid model in Rails:

• http://stackoverflow.com/questions/7086906/sorting-a-paginated-mongoid-collection-in-rails

[timoh]

[2] pry(main)> AlcoDrink.all.size
=> 448
[3] pry(main)> AlcoLocation.all.size
=> 35

Relations: 448 drinks x 35 locations => up to 15 680 queries

Actual:

[1] pry(main)> AlcoAvail.all.size
=> 11005

Due to https://github.com/timoh/craftbeer/commit/f50ff1cfe048237dffdc1f221a21b8c08d84709c

With .limit(50) and eager loading + indexing:

Local: Completed 200 OK in 957ms (Views: 394.1ms)
Heroku (app[web.1]): Completed 200 OK in 2403ms (Views: 716.5ms)

Optimal pagination could be perhaps 25 results per page to be around one second processing time on Heroku.

[timoh]

With commit https://github.com/timoh/craftbeer/commit/d7d42d4dbf2dc352df6d052028196f17a4f26c90

Now pagination with 25 drinks per page:

app[web.1]: Completed 200 OK in 1105ms (Views: 314.4ms)

..we're down to that one second on Heroku. Mission accomplished.