CODEWITHSUNDEEP
ruby-on-rails.netrubydatabasepostgresql
Ricky
Ricky

Reputation: 2907

Rails - Large database queries

Let's say I need to implement a search algorithm for a product catalog database. This would include multiple joins across the products table, manufacturers table, inventory table, etc. etc.

In .NET / MSSQL, I would isolate such logic in a DB stored procedure, then write a wrapper method in my data access layer of my .NET app to simply call this stored procedure.

How does something like this work in RoR? From my basic understanding, RoR uses its ORM by default. Does this mean, I have to move my search logic into the application layer, and write it using its ORM? The SQL stored proc is pretty intense... For performance, it needs to be in the stored procedure.

How does this work in RoR?

Edit: From the first two responses, I gather that ActiveRecord is the way to do things in Ruby. Does this mean that applications that require large complex queries with lots of joins, filtering and even dynamic SQL can (should) be re-written using ActiveRecord classes?

Thanks!

Upvotes: 2

Views: 1277

Answers (2)

Drenmi
Drenmi

Reputation: 8777

While it is possible to run raw SQL statements in Rails, using the execute method on a connection object, by doing so you will forfeit all the benefits of ActiveRecord. If you still want to go down this path, you can use it like so:

ActiveRecord::Base.connection.execute("call stored_procedure_name")

Another option to explore might be to create a "query object" to encapsulate your query logic. Inside, you could still use ActiveRecord query methods. ActiveRecord has become fairly proficient in optimizing your SQL queries, and there is still some manual tweaking you could do.

Below is a simple scaffoold for such an object:

# app/queries/search_products.rb

class SearchProducts
  def initialize(params)
    @search = search
  end

  def call
    Product.where(...) # Plus additional search logic
  end
end

The third option would be to go with something like elasticsearch-rails or sunspot. This will require some additional setup time and added complexity, but might pay off down the line, if your search requirements change.

Upvotes: 1

akbarbin
akbarbin

Reputation: 5105

Stored procedure is one of way to make the apps can be faster sometimes but it will need high costs and time to debug code for developer. Rails is using ActiveRecord ORM so if you want to use stored procedure that will lead to the main function ActiveRecord unused well.

There are some explains about rails stored-procedures-in-ruby-on-rails and using stored procedure

Upvotes: 0

Related Questions