← Back to Upcase

Advanced Query Optimization

(Upcase ) #1

Slow and redundant database queries got you down? Well have no fear! On this week's episode, Chris and Joe dive deep into the strategies you can use to optimize your queries and get your app back to ludicrous speed ASAP.

This is a companion discussion topic for the original entry at https://thoughtbot.com/upcase/videos/advanced-query-optimization

(Luke Bacon) #2

Great episode :+1: It would be helpful to know what the practical performance improvement is for people hitting this page. e.g. “They get a response 10% faster”.

(Chris Toomey) #3

Hi @equivalentideas, thanks for the question. Pretty much the cardinal rule on performance work is to always benchmark, and I failed to better highlight that in the video. Thanks for keeping me honest :smile:

Joe and I reviewed when he actually implemented the changes (the version used in the WI episode was slightly more complex) but did not capture the numbers. Looking back, it looks like we went from an average response time (from req received to response sent) went from 800ms down to 400ms. Fixed latency and rendering time added to that means an end user wouldn’t feel a full 2x improvement (likely more like 50% in end user experience) but pretty nice none the less.

(thedanotto) #4

@christoomey I finally understand eager loading. Thanks for the video again. Have you guys done any videos on bench marking? In my application, my queries are not optimized for sure, how would I go about getting a solid benchmark? When I look at the logs, the ActiveRecord load time varies anywhere from 500 ms to 600 ms, seemingly randomly. But I feel like that’s a pretty weak benchmark?


(Chris Toomey) #5

Hey @thedanotto, so glad we could help! We don’t have much on the topic of profiling, although we definitely should (and will somewhere down the line).

The best starting point I’ve found is rack-mini-profiler. I include it on all apps in dev mode at this point to gain some insight. Similarly, bullet, as mentioned in the episode, is not perfect, but can pick out some n+1s.

(Toni Rib) #6

Super useful episode! I knew about the includes style eager loading but hadn’t thought of refactoring by extracting a class or using preload to accomplish even faster queries.

(Geoff Harcourt) #7

@ToniRib I found Bryan Helmkamp’s advice in his famous blog post about refactoring code out of ActiveRecord models (http://blog.codeclimate.com/blog/2012/10/17/7-ways-to-decompose-fat-activerecord-models/) about Query Objects to be quite helpful. On a long-running project I worked on we had several very complicated queries (some using some hand-rolled SQL, others just using a lot of complicated AR logic) that we bundled up into query classes that performed the search for us.

(Andrew Charles Potter Kelley) #8

Quiet assets, which is recommended in the beginning of the post, is now deprecated.

(Victor Alfonso Hazbun Anuff) #9

Ok, this is really really good. Seriously, very impressive.