title: Ruby: Looping through timestamps and jumping back in the loop - optimization
body: <p>We have a database table in Ruby on Rails / Postgres with up to 100.000 weather data points spanning years, by the hour:</p>
  <pre><code>01/01/1999 00:00
  01/01/1999 01:00
  ...
  01/01/2000 00:00
  </code></pre>
  <p>The dates are saved in a <code>datetime</code> variable called <code>timestamp</code>.</p>
  <p>We are iterating <code>weather_data</code> and sometimes we need to jump back 1-3 hours, to check again different conditions.</p>
  <p>Then we have multiple <strong>activities</strong> that each run for 1-6 hours, depending on if the weather is good enough, or if they need to wait until the weather gets better.</p>
  <p>The user can select which day of year to start the check, but it will check from that day, for every year in the database.</p>
  <p>If the user selects '3 April 1997' it should run all the activities from that date and see how long all the activities should take.</p>
  <p>It should then repeat that process for '3 April 1998', and 1999 and all the available years in the <code>weather_data</code></p>
  <p>Some activities might take 2 hours, but they need to know the weather 4 hours in advance, even though the next activity can start after 2 hours. So there is a bit of overlap. I was hoping to solve that with variables but could not figure that out, hence I thought about 'jumping' back and forth in the loop.</p>
  <p>Simplified example:</p>
  <pre class="lang-rb prettyprint-override"><code># Collect all the years
  the_years = weather_data.map { |y| y.timestamp.year }.uniq
  
  the_years.each do |year|
    start_date = DateTime.new(year, user_input.month, user_input.day)
  
    # We could have ~100 activities
    activities.each do |activity|
      consecutive_good_weather_hours = 0
  
      weather_data.where(&quot;timestamp &gt;= ?&quot;, start_date).each do |point|
        start_date += 1.hour
  
        # checking if point.wind_speed &gt; activity.wind_speed etc.
        if weather_is_good
          # ...
          consecutive_good_weather_hours += 1
  
          # if this activity needs 3 hours of good weather, and we have 2/3
          # we go to the next data point, to check the next hour.
  
          # go to next activity if all criteria is met
          if activity_finished
            # if this activity was 3 hours long, but we were checking 2
            # hours extra into the future, we need to 'jump back' 2 hours 
            # where the next activity should start, a bit of overlap
  
            start_date -= 2.hours
            break
          end
        else
          # bad weather, reset counter, and go to next weather hour
          # try again to find x many hours of consecutive good weather
          consecutive_good_weather_hours = 0
        end
      end
    end
  end
  </code></pre>
  <p>How optimal is this?</p>
  <p>It looks like we are doing a new SQL query 300 times loading the ~100k dataset (while it shrinks a bit every time).</p>
  <p>Instead of calling <code>.where</code> all the time, could we 'jump' 3 steps backwards in the loop? If yes, how?</p>
  <p><strong>EDIT 1</strong></p>
  <p>We replaced the <code>weather_data.where(&quot;timestamp &gt;= ?&quot;, start_date).each do |point|</code> with the following:</p>
  <pre><code>while true
    point = weather_data.find_by_timestamp(year_start_date)
  </code></pre>
  <p>We also tried copying <code>weather_data</code> into an array with the <code>.to_a</code> (outside all the loops), and then do the following:</p>
  <pre><code>while true
    point = data_array.find { |i| i.timestamp == year_start_date }
  </code></pre>
  <p>But it turned out to be slower, see benchmarks.</p>
  <p>Benchmark on 20k data points and 4 activities:</p>
  <pre><code>|   Option          | points |  ms  | Allocations |
  |-------------------|--------|------|-------------|
  | where             |   20k  | 3028 |   5931134   |
  | find_by_timestamp |   20k  | 1101 |    725407   |
  | data_array.find   |   20k  | 1304 |   1393532   |
  </code></pre>
  <p>I thought the <code>find_by_timestamp</code> would be slower then the <code>array.find</code>  because it would do a <code>SELECT</code> on every point, but it looks like it's the fastest of the 3.</p>
  <p>We are using Heroku and our 1GB instance runs out of memory on bigger datasets.</p>