0

Using Rails 4.2 and PostgreSQL 9.4.5. I have a transaction log table (XLog) which I need to read in reverse, starting from the last record and continuing to process until specific conditions occur. I am able to accomplish this, but it requires an "all" method and is not efficient. In my test system, the first record appears after .06 seconds and all subsequent queries are infinitesimal, less than .001 seconds. I've tried many other queries but they don't produce the desired result. I am using ActiveRecord but could use native SQL queries for this, of course.

This is the test code which focuses only on reading XLog backwards from the last record:

class EtlLastProcessed
  def initialize(company: nil)
    ActsAsTenant.with_tenant(company) do
      count = 0
      clock = Time.now
      xlogs = XLog.all.order("created_at DESC")
      xlogs.each do |xlog|
        puts Time.now - clock
        clock = Time.now
        break if count > 1000
        count += 1
        puts "Xlog date is #{xlog.created_at.in_time_zone.to_date}"
      end
      puts "ETL Last Processed done."
    end
  end
end
Richard_G
  • 4,700
  • 3
  • 42
  • 78
  • Why don't you use just `func_each` or `find_in_batches`? For example `XLog.order("created_at DESC").find_each do` or `...find_each(batch_size: 5000) do`. Without all. – Ivan Black Mar 19 '16 at 15:54
  • Do you try `find_each(batch_size: N)` and `find_in_batches(batch_size: N)` where N > 1000? – Ivan Black Mar 19 '16 at 15:57
  • @IvanBlack Yes, I've tried both, but the query first to has get the records, then ActiveRecord will batch them for you. Neither find_each nor find_in_batches is part of the SQL query that first obtains the records that then are batched, it seems? – Richard_G Mar 19 '16 at 16:05
  • @IvanBlack I should qualify that statement to say that the to_sql method does not operate on those methods, at the least. – Richard_G Mar 19 '16 at 16:16
  • Hm, I tried this in Rails console and the query ignores the `order("created_at DESC")`condition. Rails 5.0.3-beta. I create a [paste](http://pastebin.com/6VwV2VTm) with SQL that was used. So, if you want to `order("created_at DESC")` that `find_each` is not an option. Here is a [thread](http://stackoverflow.com/questions/15189937/activerecord-find-each-combined-with-limit-and-order) about. – Ivan Black Mar 19 '16 at 16:33
  • @IvanBlack Yes, I broke the code. Editing now to resolve that question. Now that you mention it, I recall reading that point some time ago. Thanks. – Richard_G Mar 19 '16 at 16:35
  • 60 ms to fetch the records doesn't seem so bad (your code only executes one sql query) – Frederick Cheung Mar 19 '16 at 19:19
  • @FrederickCheung It does vary. But, it isn't that I disagree because I don't. It's just that: "Oh Come On! There HAS to be a better way than THIS!" Grin! – Richard_G Mar 19 '16 at 19:26

1 Answers1

0

I changed the logic to efficiently use "created_at ASC". It makes sense with my overall change in logic.

Richard_G
  • 4,700
  • 3
  • 42
  • 78