DEV Community 👩‍💻👨‍💻

mixbo
mixbo

Posted on

Rails efficient way to check if data exists

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"]]
Enter fullscreen mode Exit fullscreen mode

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"]]
Enter fullscreen mode Exit fullscreen mode

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"]]
Enter fullscreen mode Exit fullscreen mode

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"]]

Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

Hope it can help you :)

Top comments (0)

🌚 Friends don't let friends browse without dark mode.

Sorry, it's true.