DEV Community

loading...

Rails efficient way to check if data exists

ihavecoke profile image mixbo ・2 min read

Alt Text

What the ways to determine whether a record exists in the database in rails? any?, present?, empty?, exist? All of the above methods can achieve the goal but which method is more efficient? let's compare one by one below:

any?

Build.where(:created_at => 7.days.ago..1.day.ago).passed.any?

// SELECT COUNT(*) FROM "builds" 
WHERE ("builds"."created_at" BETWEEN '2017-02-22 21:22:16.885942' AND '2017-02-28 21:22:16.886077') 
AND "builds"."result" = $1 [["result", "passed"]]

This method will load COUNT(**) into memory. COUNT(**) database query is good for performance it can handle some performance problems and there are no major side effects

present?

Build.where(:created_at => 7.days.ago..1.day.ago).passed.present?

// SELECT "builds".* FROM "builds" 
WHERE ("builds"."created_at" BETWEEN '2017-02-22 21:22:27.133402' AND '2017-02-28 21:22:27.133529') 
AND "builds"."result" = $1 [["result", "passed"]]

This method will be the least efficient because it will load all data records into memory and use the instance as an AR object to determine whether the array is empty. If it is a large data table it will occupy a lot of memory in one query

empty?

Build.where(:created_at => 7.days.ago..1.day.ago).passed.empty?

//  SELECT COUNT(*) FROM "builds" 
WHERE ("builds"."created_at" BETWEEN '2017-02-22 21:22:16.885942' AND '2017-02-28 21:22:16.886077') 
AND "builds"."result" = $1 [["result", "passed"]]

This method is the same as any?

exists?

Build.where(:created_at => 7.days.ago..1.day.ago).passed.exists?

// SELECT 1 AS one FROM "builds" 
WHERE ("builds"."created_at" BETWEEN '2017-02-22 21:23:04.066301' AND '2017-02-28 21:23:04.066443') 
AND "builds"."result" = $1 LIMIT 1 [["result", "passed"]]

This is the most efficient method because it will only find the first piece of data that meets the criteria. The execution time of SELECT 1 ... LIMIT 1 in SQL is relatively short. Overall it is very effective

The following is the time consumed by the query

any?   =>  400.9 ms
present? => 2892.7 ms
empty?  =>  403.9 ms
exists? =>   1.1 ms

Based on the above results, it can be seen that exists? is the most effective under normal circumstances.

Hope it can help you :)

Discussion (0)

Forem Open with the Forem app