index.md (6537B)
1 --- 2 title: "Use .pluck If You Only Need a Subset of Model Attributes" 3 date: 2014-08-20T00:00:00+00:00 4 draft: false 5 canonical_url: https://www.viget.com/articles/pluck-subset-rails-activerecord-model-attributes/ 6 --- 7 8 *Despite some exciting advances in the field, like 9 [Node](http://nodejs.org/), [Redis](http://redis.io/), and 10 [Go](https://golang.org/), a well-structured relational database fronted 11 by a Rails or Sinatra (or Django, etc.) app is still one of the most 12 effective toolsets for building things for the web. In the coming weeks, 13 I'll be publishing a series of posts about how to be sure that you're 14 taking advantage of all your RDBMS has to offer.* 15 16 IF YOU ONLY REQUIRE a few attributes from a table, rather than 17 instantiating a collection of models and then running a `.map` over them 18 to get the data you need, it's much more efficient to use `.pluck` to 19 pull back only the attributes you need as an array. The benefits are 20 twofold: better SQL performance and less time and memory spent in 21 Rubyland. 22 23 To illustrate, let's use an app I've been working on that takes 24 [Harvest](http://www.getharvest.com/) data and generates reports. As a 25 baseline, here is the execution time and memory usage of `rails runner` 26 with a blank instruction: 27 28 $ time rails runner "" 29 real 0m2.053s 30 user 0m1.666s 31 sys 0m0.379s 32 33 $ memory_profiler.sh rails runner "" 34 Peak: 109240 35 36 In other words, it takes about two seconds and 100MB to boot up the app. 37 We calculate memory usage with a modified version of [this Unix 38 script](http://stackoverflow.com/a/1269490). 39 40 Now, consider a TimeEntry model in our time tracking application (of 41 which there are 314,420 in my local database). Let's say we need a list 42 of the dates of every single time entry in the system. A naïve approach 43 would look something like this: 44 45 ```ruby 46 dates = TimeEntry.all.map { |entry| entry.logged_on } 47 ``` 48 49 It works, but seems a little slow: 50 51 $ time rails runner "TimeEntry.all.map { |entry| entry.logged_on }" 52 real 0m14.461s 53 user 0m12.824s 54 sys 0m0.994s 55 56 Almost 14.5 seconds. Not exactly webscale. And how about RAM usage? 57 58 $ memory_profiler.sh rails runner "TimeEntry.all.map { |entry| entry.logged_on }" 59 Peak: 1252180 60 61 About 1.25 gigabytes of RAM. Now, what if we use `.pluck` instead? 62 63 ```ruby 64 dates = TimeEntry.pluck(:logged_on) 65 ``` 66 67 In terms of time, we see major improvements: 68 69 $ time rails runner "TimeEntry.pluck(:logged_on)" 70 real 0m4.123s 71 user 0m3.418s 72 sys 0m0.529s 73 74 So from roughly 15 seconds to about four. Similarly, for memory usage: 75 76 $ memory_profiler.sh bundle exec rails runner "TimeEntry.pluck(:logged_on)" 77 Peak: 384636 78 79 From 1.25GB to less than 400MB. When we subtract the overhead we 80 calculated earlier, we're going from 15 seconds of execution time to 81 two, and 1.15GB of RAM to 300MB. 82 83 ## Using SQL Fragments 84 85 As you might imagine, there's a lot of duplication among the dates on 86 which time entries are logged. What if we only want unique values? We'd 87 update our naïve approach to look like this: 88 89 ```ruby 90 dates = TimeEntry.all.map { |entry| entry.logged_on }.uniq 91 ``` 92 93 When we profile this code, we see that it performs slightly worse than 94 the non-unique version: 95 96 $ time rails runner "TimeEntry.all.map { |entry| entry.logged_on }.uniq" 97 real 0m15.337s 98 user 0m13.621s 99 sys 0m1.021s 100 101 $ memory_profiler.sh rails runner "TimeEntry.all.map { |entry| entry.logged_on }.uniq" 102 Peak: 1278784 103 104 Instead, let's take advantage of `.pluck`'s ability to take a SQL 105 fragment rather than a symbolized column name: 106 107 ```ruby 108 dates = TimeEntry.pluck("DISTINCT logged_on") 109 ``` 110 111 Profiling this code yields surprising results: 112 113 $ time rails runner "TimeEntry.pluck('DISTINCT logged_on')" 114 real 0m2.133s 115 user 0m1.678s 116 sys 0m0.369s 117 118 $ memory_profiler.sh rails runner "TimeEntry.pluck('DISTNCT logged_on')" 119 Peak: 107984 120 121 Both running time and memory usage are virtually identical to executing 122 the runner with a blank command, or, in other words, the result is 123 calculated at an incredibly low cost. 124 125 ## Using `.pluck` Across Tables 126 127 Requirements have changed, and now, instead of an array of timestamps, 128 we need an array of two-element arrays consisting of the timestamp and 129 the employee's last name, stored in the "employees" table. Our naïve 130 approach then becomes: 131 132 ```ruby 133 dates = TimeEntry.all.map { |entry| [entry.logged_on, entry.employee.last_name] } 134 ``` 135 136 Go grab a cup of coffee, because this is going to take awhile. 137 138 $ time rails runner "TimeEntry.all.map { |entry| [entry.logged_on, entry.employee.last_name] }" 139 real 7m29.245s 140 user 6m52.136s 141 sys 0m15.601s 142 143 memory_profiler.sh rails runner "TimeEntry.all.map { |entry| [entry.logged_on, entry.employee.last_name] }" 144 Peak: 3052592 145 146 Yes, you're reading that correctly: 7.5 minutes and 3 gigs of RAM. We 147 can improve performance somewhat by taking advantage of ActiveRecord's 148 [eager 149 loading](http://guides.rubyonrails.org/active_record_querying.html#eager-loading-associations) 150 capabilities. 151 152 ```ruby 153 dates = TimeEntry.includes(:employee).map { |entry| [entry.logged_on, entry.employee.last_name] } 154 ``` 155 156 Benchmarking this code, we see significant performance gains, since 157 we're going from over 300,000 SQL queries to two. 158 159 $ time rails runner "TimeEntry.includes(:employee).map { |entry| [entry.logged_on, entry.employee.last_name] }" 160 real 0m21.270s 161 user 0m19.396s 162 sys 0m1.174s 163 164 $ memory_profiler.sh rails runner "TimeEntry.includes(:employee).map { |entry| [entry.logged_on, entry.employee.last_name] }" 165 Peak: 1606204 166 167 Faster (from 7.5 minutes to 21 seconds), but certainly not fast enough. 168 Finally, with `.pluck`: 169 170 ```ruby 171 dates = TimeEntry.includes(:employee).pluck(:logged_on, :last_name) 172 ``` 173 174 Benchmarks: 175 176 $ time rails runner "TimeEntry.includes(:employee).pluck(:logged_on, :last_name)" 177 real 0m4.180s 178 user 0m3.414s 179 sys 0m0.543s 180 181 $ memory_profiler.sh rails runner "TimeEntry.includes(:employee).pluck(:logged_on, :last_name)" 182 Peak: 407912 183 184 A hair over 4 seconds execution time and 400MB RAM -- hardly any more 185 expensive than without employee names. 186 187 ## Conclusion 188 189 - Prefer `.pluck` to instantiating a collection of ActiveRecord 190 objects and then using `.map` to build an array of attributes. 191 192 - `.pluck` can do more than simply pull back attributes on a single 193 table: it can run SQL functions, pull attributes from joined tables, 194 and tack on to any scope. 195 196 - Whenever possible, let the database do the heavy lifting.