Optimize the query for better performance

Question

So I have this function.

Let say I have millions of posts.

How can I optimize this function?

def fun
  Post.all.each do |post|
    if post.user.present?
       post.active = true
    else
       post.active = false
    end
    post.save
  end
end

Like do this in fewer line with better performance because this is not a very good approach.

Answer 1

This should do the trick - and it's FAST...

Post.update_all("active = (user_id IS NOT NULL)")

Answer 2

Here's another option that does it in two queries without any raw SQL (just plain ol' Rails):

Post.where(user_id: nil).update_all(active: false)
Post.where.not(user_id: nil).update_all(active: true)

And, believe it or not, this actually runs faster in the database than doing it in one query that's using an expression – active = (user_id IS NOT NULL) – to populate active.

Here are the speed results from testing on a table with only 20,000 records:

# Single (expression-based) query
<Benchmark::Tms:0x00007fd251a52780 @cstime=0.0, @cutime=0.0, @label="", @real=2.3656239999982063, @stime=0.0, @total=0.009999999999999787, @utime=0.009999999999999787>

# Two (purely column-based) queries
<Benchmark::Tms:0x00007fd2518c36d0 @cstime=0.0, @cutime=0.0, @label="", @real=2.309347999995225, @stime=0.0, @total=0.0, @utime=0.0>