RMR12: Ruby vs. SQL

Transcript

1. Today I want to talk about algorithms.
2. Today I want to talk about algorithms. an algorithm.
3. My name is Gordon Diggs.
4. I work here: www.paperlesspost.com @paperlesspost / @paperlessdev
5. But this talk isn't about that.
6. I collect these:
7. But I started getting a lot of them
8. So I made this: vinyl.gordondiggs.com
9. It makes these: ooooo...pretty!
10. At first, I did this settings.fields.each do |field| occurrences[field] = {} Item.all.each do |item| value = item.send(field.robotize) if occurrences[field][value] occurrences[field][value] += 1 else occurrences[field][value] = 1 end end if (others = occurrences[field].select{|k,v| v==1}.size) > 1 occurrences[field].delete_if{|k,v| v==1} occurrences[field]["Other"] = others end end
11. That sucks! Super slow
12. That sucks! With 9000 items, it takes ~23.4457 seconds
13. OK, fine. What do? Reduce the need to do it: cache the result, only recalculate when something changes
14. But it's still slow! Find another way to do it. Step 1: ditch mongodb for postgres
15. A new dawn awaits SQL has cool stuff, like counting and grouping (we want that, right?)
16. The new solution settings.fields.each do |field| occurrences[field] = {} items = DataMapper.repository.adapter.select( "SELECT #{field.robotize} as \"col\", COUNT(*) as \"times\" FROM items GROUP BY #{field.robotize} ORDER BY \"times\" desc") items.each do |item| occurrences[field][item[:col]] = item[:times] end if (others = occurrences[field].select{|k,v| v==1}.size) > 1 occurrences[field].delete_if{|k,v| v==1} occurrences[field]["Other"] = others end end
17. How fast is that? With the same 9000 items, it takes ~0.4663 seconds
18. Why is that? Because SQL is way better at this than we are.
19. The Benefits • Can run it whenever I want without repercussions • Scales way better • Can stare at graphs all day
20. The Takeaway • There are things that ruby does really well, and things that SQL does better.
21. Thank you. @gordondiggs